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

Folks €“
Im 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. Im 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

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

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

it is still a range

--
__________________________________
HTH

Bob

"Mike G - DC" wrote in message
...
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

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

excellent. Thankyou

"Bob Phillips" wrote:

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

it is still a range

--
__________________________________
HTH

Bob

"Mike G - DC" wrote in message
...
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

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

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

"Bob Phillips" wrote:

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

it is still a range

--
__________________________________
HTH

Bob

"Mike G - DC" wrote in message
...
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

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

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 wrote:

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

"Bob Phillips" wrote:

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

it is still a range

--
__________________________________
HTH

Bob

"Mike G - DC" wrote in message
...
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





--

Dave Peterson

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

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

"Dave Peterson" wrote:

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 wrote:

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

"Bob Phillips" wrote:

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

it is still a range

--
__________________________________
HTH

Bob

"Mike G - DC" wrote in message
...
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





--

Dave Peterson