using vlookup in vba

[oercim]

Hello, I have a problem. I want to use vlookup function in a loop in vba. Let say:

For i = 1 To 10
Sheets("Sheet1").Cells(i,2)) = "=VLOOKUP(C[-2],Ä°LK1!C[-1]:C[54],i,0)"
Next

The problem is that in "VLOOKUP(C[-2],Ä°LK1!C[-1]:C[54],i,0)" "i" is not recognized but if it is assigned as integer it works but I need it to be varible. If you help me, I will be very glad. Thanks a lot.

 

[Maurizio Borrelli]

Hi oercim,

Sheets("Sheet1").Cells(i,2)).Formula = "=VLOOKUP(C[-2],Ä°LK1!C[-1]:C[54]," & CStr(i) & ",0)"

 

[oercim]

Thank you ver much Maurizio. This was very important to me.

 

[Claus Busch]

Hi Oercim,

Am Thu, 7 Feb 2013 00:46:43 -0800 (PST) schrieb oercim:

For i = 1 To 10
Sheets("Sheet1").Cells(i,2)) = "=VLOOKUP(C[-2],?LK1!C[-1]:C[54],i,0)"
Next

why not use formula in B1 and fill down to B10?
With Sheets("Sheet1")
.Range("B1").Formula = "=VLOOKUP(A1,'ILK1'!A1:BD1,Row(A1),0)"
.Range("B1").AutoFill .Range("B1:B10")
End With


Regards
Claus Busch

 

[oercim]

Hello.I live another problem. I needed to change the code:

It was like that:
For i = 1 To 10
Sheets("Sheet1").Cells(i,2)) = "=VLOOKUP(C[-2],Ä°LK1!C[-1]:C[54],i,0)"
Next

After:

For i = 1 To 10
Sheets("Sheet1").Cells(i,2)) = "=VLOOKUP(C1,Ä°LK1!C2:C54," & CStr(i) & ",0)"
Next

In second one it doesn't do the job. It doesnt join the tables. Why can this be? Thanks a lot.

 

[Claus Busch]

Hi,

Am Thu, 7 Feb 2013 01:45:51 -0800 (PST) schrieb oercim:

For i = 1 To 10
Sheets("Sheet1").Cells(i,2)) = "=VLOOKUP(C1,?LK1!C2:C54," & CStr(i) & ",0)"
Next

you have an error in the syntax:
For i = 1 To 10
Sheets("Sheet1").Cells(i, 2).Formula = " _
=VLOOKUP(C1,'ILK1'!C2:C54," & i & ",0)"
Next


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Thu, 7 Feb 2013 10:52:36 +0100 schrieb Claus Busch:

For i = 1 To 10
Sheets("Sheet1").Cells(i, 2).Formula = " _
=VLOOKUP(C1,'ILK1'!C2:C54," & i & ",0)"
Next

if you want to suit the references in every new row:
With Sheets("Sheet1")
.Range("B1").Formula = "=VLOOKUP(C1,'ILK1'!C2:C54,ROW(A1),0)"
.Range("B1").AutoFill .Range("B1:B10")
End With


Regards
Claus Busch

 

[Claus Busch]

Hi again,

Am Thu, 7 Feb 2013 10:52:36 +0100 schrieb Claus Busch:

For i = 1 To 10
Sheets("Sheet1").Cells(i, 2).Formula = " _
=VLOOKUP(C1,'ILK1'!C2:C54," & i & ",0)"
Next

in Excel 2007 or later versions ILK1 is a cell reference. Therefore you
have to put it in apostrophes.


Regards
Claus Busch

 

[oercim]

Thanks for trying help Claus.I m using Excel 2003. I tried your suggestions but, I could not manage. It returns "0" as mine.

 

[Maurizio Borrelli]

Il giorno giovedì 7 febbraio 2013 10:08:08 UTC+1, oercim ha scritto:

Thank you ver much Maurizio. This was very important to me.

YW

 

[oercim]

I solved the problem. Why it is solved I dont know but it is solved.

The working code is as below:

For i = 1 To 10

Sheets("Sheet1").Cells(i,2)) = "=VLOOKUP(C1,Ä°LK1!C2:C[54]," & i & ",0)"
Next

Thanks for the suggestions. They were very helpful