Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I've created an Excel worksheet using the Vlookup command. | Excel Worksheet Functions | |||
using a vlookup command in a for next loop in a macro in excel | Excel Discussion (Misc queries) | |||
Loop command in Macro | Excel Programming | |||
loop command in Macro | Excel Programming | |||
loop command in Macro | Excel Programming |