Cell address from VLookup

[smiller0422]

I'm using the VLookup function in a macro. If a data element from sheet1 is found in sheet2's lookup range, I would like to save/store the cell's location from sheet2 in a variable.

 

[Domenic]

To store the range object in a variable, try something like this...

Set r = Evaluate("index(sheet2!a1:a100,match(sheet1!b2,sheet2!a1:a100,0))")

 

[smiller0422]

I need the exact row of the first 'found' data element.

 

[Domenic]

For the row number, you can then use...

r.Row

 

[smiller0422]

I'm getting Runtime Error 13 Type Mismatch

 

[Domenic]

Try...

[font=Verdana]    [color=darkblue]Dim[/color] Rng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Rw [color=darkblue]As[/color] [color=darkblue]Long[/color]

    [color=darkblue]Set[/color] Rng = Evaluate("index(sheet2!a1:a100,match(sheet1!b2,a1:a100,0))")
    
    Rw = Rng.Row
    [/font]