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

On 27 Jun, 17:13, wrote:
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

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

TIA

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



wrote:

cell address of a succesful VLookup

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)


" wrote:

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

Thanks Aiden and especially, Tom


Tom Ogilvy wrote:

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

Everything you show are worksheet implementations. Not VBA.

It is unclear what you are asking.

--
Regards,
Tom Ogilvy



" wrote:

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

TIA

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



wrote:

cell address of a succesful VLookup

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

Tom Ogilvy wrote:

Everything you show are worksheet implementations. Not VBA.

It is unclear what you are asking.

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


" wrote:

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

Tom Ogilvy wrote:

Everything you show are worksheet implementations. Not VBA.

It is unclear what you are asking.

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

Tom Ogilvy wrote:

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.