CODEWITHSUNDEEP
vbaexcelruntime-error
rngeta453
rngeta453

Reputation: 11

VBA loop fails around 88th iteration

I have the following Sub, which works fine if the range it's covering is fewer than 88 cells, otherwise it fails around the 88th iteration.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 And Target.Row >= 3 And Target.Row <= 30 And Target.Column >= 17 And Target.Column < 22 Then

    i = Target.Row

    Dim MergeGroups As Range
    Dim GroupTable As Range
    Dim rngStart As Range
    Dim rngEnd As Range
    Dim rngToCount As Range
    Dim CurrentGrp As Range
    Dim NextGrp As Range
    Dim NumVals As Integer

    Set MergeGroups = Range("A1:O1")
    Set GroupTable = Range("Q2:V2")
    Set CurrentGrp = Range(Cells(GroupTable.Row, ActiveCell.Column).Address)
    Set NextGrp = Range(Cells(GroupTable.Row, ActiveCell.Column + 1).Address)
    Set rngStart = MergeGroups.Find(CurrentGrp.Value)
    Set rngEnd = MergeGroups.Find(NextGrp.Value)

    Set rngToCount = Range(Cells(ActiveCell.Row, rngStart.Column), Cells(ActiveCell.Row, rngEnd.Column - 1))
    '    rngToCount.Font.Bold = True

    NumVals = Application.WorksheetFunction.CountA(rngToCount)

    Cells(i, ActiveCell.Column).Value = NumVals
    ActiveCell.Offset(1, 0).Select
    Do While ActiveCell.Column < 21
        ActiveCell.Offset(-28, 1).Select
    Loop       

End If

End Sub

It's an object in one specific worksheet, making use of the SelectionChange event. When it fails, I get the error message:

Run-time error '-2147417848 (80010108)': Method 'Find' of object 'Range' failed.

The issue is with the line:

Set rngStart = MergeGroups.Find(CurrentGrp.Value)

Can anyone help me work out why the Sub runs fine for small ranges, but otherwise fails around a particular iteration?

Upvotes: 1

Views: 120

Answers (1)

rngeta453
rngeta453

Reputation: 11

Credit to @Mat's Mug for telling me what was wrong, and this Q&A for helping me put it right. This is the modified code:

Sub PleaseWorkThisTime()

Dim MergeGroups As Range
Dim GroupTable As Range
Dim GrpCounts As Range
Dim rngStart As Range
Dim rngEnd As Range
Dim rngToCount As Range
Dim CurrentGrp As Range
Dim NextGrp As Range
Dim NumVals As Integer

Set MergeGroups = Range("A1:O1")
Set GroupTable = Range("Q2:V2")
Set GrpCounts = Range("Q3:U23")

Dim GrpCount As Range
For Each GrpCount In GrpCounts

Set CurrentGrp = Range(Cells(GroupTable.Row, GrpCount.Column).Address)
Set NextGrp = Range(Cells(GroupTable.Row, GrpCount.Column + 1).Address)
Set rngStart = MergeGroups.Find(CurrentGrp.Value)
Set rngEnd = MergeGroups.Find(NextGrp.Value)
Set rngToCount = Range(Cells(GrpCount.Row, rngStart.Column), Cells(GrpCount.Row, rngEnd.Column - 1))

NumVals = Application.WorksheetFunction.CountA(rngToCount)

GrpCount.Value = NumVals

Next GrpCount

End Sub

Upvotes: 0

Related Questions