Dear Dr. GUI:

I'm not sure if this is one for Dr. GUI, tech support, or whomever, but I would like an answer.

Now that I've done the Paradigm Shuffle (that is, started programming Microsoft Excel macros in Visual Basic for Applications), I've run into a problem: How do I verify that a Find method actually found something? What I would like to do is save the resulting range (cell) in an Object variable, then test to see if it has been set. (This is equivalent to doing a FORMULA.FIND in the old macro language and branching based on the result.) I have not found any way to determine whether the Object variable has been set.

Thanks,

Tom Berube

Dr. GUI replies:

You may wish to check out article Q108892 in the Knowledge Base, but the following code illustrates some other solutions:

Dim ResultCell as Range
Set ResultCell = Range("a:a").Find("GUI")
If ResultCell Is Nothing Then
  MsgBox "GUI not found"
End If

or

On Error Resume Next
Set found = Cells.Find(What:="GUI")
If IsError(found.Address) Then
  MsgBox "GUI not found"
Else
  MsgBox found.Address
End If
On Error Go To 0