Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
Hi oercim,
Sheets("Sheet1").Cells(i,2)).Formula = "=VLOOKUP(C[-2],İLK1!C[-1]:C[54]," & CStr(i) & ",0)" -- Ciao! Maurizio Il giorno giovedì 7 febbraio 2013 09:46:43 UTC+1, oercim ha scritto: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
Thank you ver much Maurizio. This was very important to me.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
Thanks for trying help Claus.I m using Excel 2003. I tried your suggestions but, I could not manage. It returns "0" as mine.
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
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 -- Ciao! Maurizio |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
using vlookup in vba
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup problem - unable to get the vlookup property | Excel Programming | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel |