Something like:

Dim ws1 as worksheet, ws2 as worksheet

Set ws1=Worksheets("Sheet1")
Set ws2=Worksheets("Second")


cell(i,4)=application.vlookup(ws1.cells(irow,"A"), ws2.range("A:C"),3,0)

(Not sure about the Cells(irow,"A") ... but this is the lookup value .. set
'irow 'as needed)

HTH


"Amethyst" wrote:

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