excel vba .find not returning range

[ksharpe212]

I am trying to use the .find function in excel vba. I am trying to find the first occurrence of a name in a list of names.

I have declared a range variable and set up the .find function statement (with the required parameters (attributes?)) and finally gotten it to run through the code with no error.

However, if I query the range variable (?find_shaun) in the immediate window, it returns the value in the cell where it found the name being sought. It doesn't return a range.

I tried to select it and thought I could query the range variable for the row but trying to select it, I get an error: method 'Range' of object _'Global' failed. It doesn't seem to be a range reference at all, just the value of the name it was searching for.

Here is my code:

Sub find_name()
'
' macro find_name searches for a trainer name in the trainer list in table2
'
Dim a As Integer
Dim find_shaun As Range
Dim trainer_name As Range
Dim trainer As String

Sheets("Sheet1").Select
' Set find_shaun = Range("table2[trainer]")
Set trainer_name = Range("g2:g2")
Range("table2[trainer]").Select
With ActiveSheet
Set find_shaun = Selection.Find( _
What:=trainer_name, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
End With

Range("find_shaun").Select '<--- here is where I get the method of 'range' of object _'global' failed
a = ActiveCell.Row

End Sub

Can anyone tell me what I've done wrong or what I'm missing in the functioning of the find function.

thanks.

 

[Marcelo Branco]

Hi,

You have defined (Dim) find_shaun as Range, so i think you can simply use
a = find_shaun.row

and delete
Range("find_shaun").Select

HTH

M.

 

[Marcelo Branco]

Complementing my previous post

better to check if anything was found, so

If Not find_shaun Is Nothing then
    a = find_shaun.Row
End If

M.