For loop vlookup

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.

Dim SrcRange As Range
Dim LkRange As Range
Dim x As Long


Set SrcRange = Sheets(1).Range("B1:B5")
Set LkRange = Sheets(2).Range("A1:B5")

For x = 1 To SrcRange.Cells.Count
    SrcRange.Cells(x).Formula = "=VLOOKUP(x,LkRange,2,0)"
Next

Thanks,
amphinomos

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

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 ?

Dim SrcRange As Range
Dim LkRange As Range
Dim x As Long


Set SrcRange = Sheets(1).Range("A1:B5")
Set LkRange = Sheets(2).Range("A1:B5")

For x = 1 To SrcRange.Cells.Count
    SrcRange.Cells(x).Formula = "=VLOOKUP(" & x & "," & LkRange.address & ",2,0)"
Next

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

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

Try:

Sheets(1).Range("B1:B5").Formula = "=VLOOKUP(A1,Sheet2!$A$1:$B$5,2,0)"

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

Dim SrcRange As Range
Dim RsRange As Range
Dim LkRange As Range
Dim x As Long


Set SrcRange = Sheets(1).Range("A1:A5")
Set RsRange = Sheets(1).Range("B1:B5")
Set LkRange = Sheets(2).Range("$A$1:$B$5")

For x = 1 To RsRange.Cells.Count
    RsRange.Cells(x).Formula = "=VLOOKUP(" & SrcRange.Address & "," & LkRange.Address & ",2,0)"
Next
End Sub

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).

Any reason you can't use it like this?

Sub amphinomos()

Dim RsRange As Range
Dim LkRange As Range

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

RsRange.Formula = "=VLOOKUP(A1," & LkRange.Address & ",2,0)"

End Sub

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

Sub macro_1()
Sheets(1).Range("B1:B5").Formula = "=VLOOKUP(A1,Sheet2!$A$1:$B$5,2,0)"
End Sub

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 !