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

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

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

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

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

Could we not use something a bit simpler?

"Toppers" wrote:

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

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

What's complex about this ... it's just a basic VLOOKUP command using VBA;
you can't make it much simpler.

You might remove the reference to ws1 (but this assumes the right sheet is
"active") but there isn't much else you can do if you want to use VLOOKUP


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


"Amethyst" wrote:

Could we not use something a bit simpler?

"Toppers" wrote:

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