Find method object variable or with block variable not set in excel VBA

Question

I got this example straight from the Microsoft site, it's the first example on how to use the find method. The problem is it gives error 91: object variable or with block variable not set. It's supposed to find where a cell has a value of 2 and change it to 5 and it does that if there are any "2"s in the range but then it also gives the error when it's done. What am I doing wrong?

Sub example()

With Worksheets(1).Range("a1:a10")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
    firstAddress = c.Address
    Do
        c.Value = 5
        Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
  End If

   End With

End Sub

There is an error in that example, caused by replacing the value with 5: it works if you don't do this because the find keeps looping back to the first location, meaning c is never Nothing once you're in the loop. Whoever wrote that example never tested it, and it shows up here on SO every few months;-) — Tim Williams

Crowcoder Crowcoder · Accepted Answer · 2015-02-08T16:51:20

Your problem is this line:

Loop While Not c Is Nothing And c.Address <> firstAddress

VB will evaluate all expressions so if c is Nothing, it will still evaluate c.Address and that throws an error because you cannot access a property of a null value. You need to refactor the code to check each "And" condition in a different block.

Find method object variable or with block variable not set in excel VBA

1 Answers