MS used a different example in earlier versions of its help. Instead of
changing the value, it changed the font (or color or something).

But when it changed the action, it broke the code. You diagonosed the problem
very well: If c is nothing, then c.address will break the code (nothing doesn't
have an address!).

I'd use:

Option Explicit
Sub Test()
Dim C As Range
Dim FirstAddress As String

With Worksheets(1).UsedRange
Set C = .Find("Ian", LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
C.Value = "Tom"
Set C = .FindNext(C)
If C Is Nothing Then
Exit Do
Else
If FirstAddress = C.Address Then
Exit Do
End If
End If
Loop
End If
End With
End Sub

In fact, I'd recommend that you specify all the parms on that .find statement.
If you don't, then you'll inherit the last settings that your code used -- or
what the user did manually.

And if you're doing this for a real reason, using a "replace all" would be
quicker than looping.

IanKR wrote:

When I run the Find method example from the VBA Help I get:

"Run-time error '91': Object variable or With block variable not set"

viz:

Sub Test()
With Worksheets(1).UsedRange
Set c = .Find("Ian", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = "Tom"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

It replaces all the "Ian"s that it finds with "Tom"s, but presumably once it
has replaced the last one (i.e. once c eventually becomes Nothing, and
therefore c.Address doesn't exist) it fails. I think that is the
explanation, because it works without an error if I remove the

And c.Address < firstAddress

from the end of the Loop While line, so I'm doing it like that in my
project. Is this an example of VBA Help giving duff information? In which
case, not only is the removed bit surplus to requirements, but also wrong?!

Thanks

Ian

--

Dave Peterson