using a vlookup command in a for next loop in a macro in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help me with the correct syntax for this?
I'm using Office 2003......

Here is my problem:

Ok, I start with a string of text and fill in columns using a “=midâ€
formula…

finalrow = Cells(65536, 1).End(xlUp).Row
For i = 1 To finalrow
MyText = Cells(i, 1)

Cells(i, 2).Value = Mid(MyText, 18, 3)
x = Mid(MyText, 18, 3).value
Cells(i, 3).Value = Mid(MyText, 10, 2)


So I now have my value for columns 2 and 3… and I have the variable x set
to the value of column 2.

Now I want to use a “vlookup†command to search another worksheet for the
value of column 4.
(call the other worksheet, “second†and the value looking up is in sheet 1,
column “aâ€, returning the value in column “câ€)

So if the value of column 4 equals “y†then

Cells(I, 4).Value = y

Next i

Thanks in Advance for all your help.....
 
Your vlookup should look something like:

Cells(i, 4).Value = Application.Vlookup(x, Sheets("Second").Range("A:C"), 3,
False)
 
Close, but still not quite right....

Second is the name of my 2nd workbook, and I need a range from "sheet1" of
that workbook...

I'm still not getting it quite right, it keeps erroring out on this line of
code at the 2nd workbook file name....
 
Then why did you say "Call the other worksheet 'second'"?

If it's a workbook try:

Dim Second as workbook

Set Second = workbooks.open (...)

Cells(i, 4).Value = Application.Vlookup(x, Second.Sheets("Sheet1").Range("A:C"),
3, False)
 
your right, I'm sorry, I had been working on this problem for hours and I
was tired so I wrote it down incorrectly.

I tried the new code, and now it's erroring out on this line:

Set Second = workbooks.open (...)

.... is supposed to be the actually file name? correct?

Thanks,
 
Back
Top