Error when running VBA Help's Find method example

[IanKR]

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]

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]

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.

Many thanks for your quick reply, Dave. Yes - I shall be declaring all
variables in my project, and the whole thing will be "locked down", thereby
not allowing the user to do any Find/Replace from the UI. At the moment (but
it's early stages) I don't think I'll be using Find/Find Next code
elsewhere, but if I do, I shall specifiy all the parms as you say - I'd
noted this from the Help.
Thanks again.
Ian

 

[Jim Thomlinson]

Just to add to Dave's post I tend to set all of the parameters of the find
that I require. If you are finding all of the instances (as your code does)
then you don't care about the SearchOrder or the Search Direction or After.
But if you define those in code then you potentially changing the last value
that the end user had set for those parameters and the next time the user
does a find they have to reset those things back again. I try to keep my big
nose out of as many things as I reasonably can... Note that there are times
when you may need to define those parameters though...

 

[Dave Peterson]

I actually meant that you could do the replace all in your code.