VBA loop fails around 88th iteration

Question

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?

You are calling the same sub each time you select another cell in the loop. You need to either figure out how to do this without using select, or disable the events while you do the loop and enable them before exiting the sub. — Scott Craner, May 08 '17 at 16:06
Many thanks to @Mat'sMug! I must confess I didn't fully understand the reply, but it set me in the right direction - [this link](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) was helpful because I couldn't understand what was so evil about `.Select`. I think I have it working now, I will post my new code here as soon as I've tested it properly. — rngeta453, May 09 '17 at 13:32

score 0 · Answer 1 · edited May 23 '17 at 11:47

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

VBA loop fails around 88th iteration

1 Answers1