Error While Using FindNext

[kingnutin]

Using Excel 2007 in an xlsm format.

I am having trouble with find routine. I get the old "object variable or with block variable not set" error on my "Loop While Not formulaRng Is Nothing And formulaRng.Address <> firstaddress" section of the code.

If I comment out the code

Worksheets("SVG").Range("A" & r - 9 & ":" & "B" & r + 1).ClearContents

everything works fine.

I know that there are several duplicate instances of the search term in the selected range. It is as if it searches for all the instances, but after it comes to the last one and searches again it fails when I use the code above.

When the error occurs the variables have the following values:
formulaRng = Nothing
formulaRng.Address = Object variable or With block variable not set
firstAddress = "$A$39495"

Any ideas on how to clear out the contents of a range of cells and make this work? I tried setting there value to just "", but didn't have any luck.

Code:

Dim formulaRng As Range
Dim formula(1 To 7) As String
Dim firstFormula As Long
Dim firstAddress as String
dim j as integer
dim r as long

firstFormula = 99999
formula(1) = "(MAX(((IN8 - IN7))"
formula(2) = "((MAX(((IN7 - IN6))"
formula(3) = "((MAX(((IN6 - IN5))"
formula(4) = "((MAX(((IN5 - IN4))"
formula(5) = "((MAX(((IN4 - IN3))"
formula(6) = "((MAX(((IN3 - IN2))"
formula(7) = "((IN2 - IN1)))))))))))))))))))"

With Worksheets("SVG").Range("A1:A40000")
    For j = 1 To 7
        Set formulaRng = .Find(formula(j), LookIn:=xlValues)
        If Not formulaRng Is Nothing Then
            firstaddress = formulaRng.Address
            Do
                If formulaRng.Row < firstFormula Then
                    firstFormula = formulaRng.Row - 9
                End If
                r = formulaRng.Row
                'Clear contents of cells
                Worksheets("SVG").Range("A" & r - 9 & ":" & "B" & r + 1).ClearContents
                Set formulaRng = .FindNext(formulaRng)
            Loop While Not formulaRng Is Nothing And formulaRng.Address <> firstaddress
        End If
    Next j
End With

If firstFormula < 99999 Then
    Worksheets("Comments").Range("A1:B52").Copy Destination:=Worksheets("SVG").Range("A" & firstFormula)
End If

 

[Andrew Poulsom]

If you are clearing the contents it won't be found again at firstaddress so you just need:

Loop While Not formulaRng Is Nothing

 

[kingnutin]

Ah! Thanks!