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
 

Your vlookup should look something like:

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

--
Regards,
Fred


"Amethyst" wrote in message
...
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
 

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" wrote:

Your vlookup should look something like:

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

--
Regards,
Fred


"Amethyst" wrote in message
...
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
 

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)


--
Regards,
Fred


"Amethyst" wrote in message
...
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" wrote:

Your vlookup should look something like:

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

--
Regards,
Fred


"Amethyst" wrote in message
...
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
 

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" wrote:

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)


--
Regards,
Fred


"Amethyst" wrote in message
...
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" wrote:

Your vlookup should look something like:

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

--
Regards,
Fred


"Amethyst" wrote in message
...
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
 

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

--
Regards,
Fred


"Amethyst" wrote in message
...
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" wrote:

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)


--
Regards,
Fred


"Amethyst" wrote in message
...
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" wrote:

Your vlookup should look something like:

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

--
Regards,
Fred


"Amethyst" wrote in message
...
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.....