Application.Match - Is it possible to use named ranges?

[Mike G - DC]

Folks –
I’m working to answer my own question in a posting yesterday, Subject:
Add/Update data stored in another worksheet 7/7/2009 2:09 PM PST, (Shameless
Bump).

The first step in resolving my issue is to identify if a part number in
sheet 1 “TSCREEN†range A11:A24 is in the range A:A in sheet2 (TDATA). I
found the following code in another post which gets me close. I’m wondering
if I can use either a named range or cell reference within the match function
rather than actually listing the specific part name in the code.

Dim R As Variant 'could be an error
Dim Trange As Range
Set Trange = Sheets("TDATA").Range("A:A")
R = Application.Match("axle", Trange, 0)
If IsError(R) Then
MsgBox "not found"
Else
MsgBox "found"
End If

Any help is much appreciated - mike

 

[Bob Phillips]

R = Application.Match(Range("axle"), Trange, 0)

it is still a range

 

[Mike G - DC]

excellent. Thankyou

R = Application.Match(Range("axle"), Trange, 0)

it is still a range

--
__________________________________
HTH

Bob

 

[Mike G - DC]

Bob - Is it possible to use a formula within Application.Match to identify
the string to look for? The following code runs, but the match is not found
even though it is present within Trange.
Thanks, Mike

Dim R As Variant 'could be an error
Dim Trange As Range

Set Trange = Sheets("TOURDATA").Range("A:A")

R = Application.Match(FormulaR1C1 = "=CONCATENATE(userid,dayone)", Trange2, 0)

If IsError(R) Then
MsgBox "Not found"
Else
MsgBox "found"
End If

End Sub

 

[Dave Peterson]

I'm not sure what userid and dayone are, but maybe:

R = Application.Match(userid & dayone, Trange2, 0)
or
R = Application.Match(range("userid").value & range("dayone").value, Trange2, 0)

 

[Mike G - DC]

Bingo! R = Application.Match(range("userid").value & range("dayone").value,
Trange2, 0). userid and dayone are named ranges. Also, I noticed that Trange2
should be Trange. Not sure where the 2 came from.

Thanks so much for your advice.
mike