How to obtain cell address of a succesful VLookup?

[EagleOne]

2003/2007

If the following is successful:

=VLOOKUP(J68,'1000'!$J$139:$J$150,1,FALSE)

How can I easily ID the found cell reference to use in subsequent VBA?

TIA EagleOne

 

[aidan.heritage]

2003/2007

If the following is successful:

=VLOOKUP(J68,'1000'!$J$139:$J$150,1,FALSE)

How can I easily ID the found cell reference to use in subsequent VBA?

TIA EagleOne

Not sure why you want to, but MATCH would do it

 

[EagleOne]

I believe I found a clue. Need to prove it.

TIA

Address(match(J68,'1000'!$J$139:$J$150,0),1)

 

[Guest]

Dim res as Variant, rng as Range
res = Application.Match(Range("J68").Value, _
Worksheets("1000").Range("J139:J150"),0)
if not iserror(res) then
set rng = Worksheets("1000").Range("J139:J150")(res)
else
set rng = Nothing
end if

--
Regards,
Tom Ogilvy


=VLOOKUP(J68,'1000'!$J$139:$J$150,1,FALSE)

 

[EagleOne]

Thanks Aiden and especially, Tom

 

[Guest]

Everything you show are worksheet implementations. Not VBA.

It is unclear what you are asking.

 

[EagleOne]

Good point.

What I need is either VBA variable [or in the activecell.Offset(,1)] the actual w/s cell reference
which contains the match (previously VLookup)

Using Address(Match( ......)) I get the relative Address i.e. "A1" which is actually "J139" of w/s
1000

 

[Guest]

My code gives you that.

Dim res as Variant, rng as Range
res = Application.Match(Range("J68").Value, _
Worksheets("1000").Range("J139:J150"),0)
if not iserror(res) then
set rng = Worksheets("1000").Range("J139:J150")(res)
else
set rng = Nothing
end if
if not rng is nothing then
msgbox "Match made at " & rng.Address
else
msgbox "No match made"
End if

Will give you some visual stimulation.

--
Regards,
Tom Ogilvy

Good point.

What I need is either VBA variable [or in the activecell.Offset(,1)] the actual w/s cell reference
which contains the match (previously VLookup)

Using Address(Match( ......)) I get the relative Address i.e. "A1" which is actually "J139" of w/s
1000

Everything you show are worksheet implementations. Not VBA.

It is unclear what you are asking.

 

[EagleOne]

Zing; Pop; Bang; it is the 4th of July!