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

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

 

[Fred Smith]

Your vlookup should look something like:

Cells(i, 4).Value = Application.Vlookup(x, Sheets("Second").Range("A:C"), 3,
False)

 

[Guest]

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

 

[Fred Smith]

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)

 

[Guest]

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,

 

[Fred Smith]

Yes, replace the ... with the proper Open command parameters.