Excel Find not working

[Phantek]

I used a recorded macro to try and learn how to use the Find command in VBA. Everything works fine if I want to activate the cell containing the information, like this:

Dim fullName as String

Selection.Find(What:=fullName, After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False).Activate

Unfortunately, when I try to assign the address to a variable, it never assigns anything... such as:

Dim rng as Range, fullName as String

rng = Selection.Find(What:=fullName, After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False).Address

Any idea what I'm doing wrong?

 

[SkipVought]

hi,

Dim rng as [b]String[/b], fullName as String

rng = Selection.Find(What:=fullName, After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False).Address

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

You asked for a way to get the address, and Skip's code should do that for you.

If you do, however, want to assign the found cell to a range variable, keep your initial code and use:

Set rng = Selection. etc

In general to look through all the cells returned by Find:

From the help (with a minor mod):

Example
This example finds all cells on worksheet one that contain the value 2 and changes it to 5.

With Worksheets(1).Cells
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
End If
End With

Tony

 

[SkipVought]

Here is the metod that I oftne use for the Find Method...

dim rFound as range

set rFound = MySheetObject.Cells.Find("Something")

If not rFound is nothing then
  'found soemthing

End if

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]