For loop vlookup

[amphinomos]

Hi,

I'm trying to create a vlookup on multiple. It's pretty straightforward but I get a #NAME error. I'm pretty sure it has to do with my counter variable but need help on fixing it.

Please Login or Register  to view this content.

Thanks,
amphinomos

[ragulduy]

I think you probably need:
"=VLOOKUP(" & x & "," & LkRange.address & ",2,0)"

[amphinomos]

Thanks ragulduy. I get a #N/A error but that's probably because I've set up my ranges/counter wrong. What I would like to do is vlookup Sheet1!A1:A5 (I've changed SrcRange to A1:B5) with Sheet2!A1:B5 and put the values in Sheet1!B1:B5. As I've set it up, I'm doing a vlookup of the counter itself ?

Please Login or Register  to view this content.

[JOHN H. DAVIS]

I think you would need dollar signs when you set your range.

Set LkRange = Sheets(2).Range("$A$1:$B$5")

[ragulduy]

Try:

Please Login or Register  to view this content.

[amphinomos]

Thanks raguldy. I've modified the code but still can't get it to perform what I'd like :

Please Login or Register  to view this content.

If I check in Sheet1!B1 it's currently giving me this formula =VLOOKUP($A$1:$A$5,$A$1:$B$5,2,0) where I would need =VLOOKUP(Sheet1!A1,Sheet2!$A$1:$B$5,2,0).

[ragulduy]

Hi, that is not the code I posted. You need:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Any reason you can't use it like this?

Please Login or Register  to view this content.

[amphinomos]

Thanks guys. Ragulduy your code works fine...didn't realize it should've been so simple.
JOHN I've also tried your code but the Address method only returns in the vlookup $A$1:$B$5 and not Sheet2!$A$1:$B$5.
In any case many thanks !