| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Specifying cell(s) for Vlookup Function in Macro
I'm trying to write a macro that will insert a column and formula for all
cells in a list. The number of rows in the list will vary (so I have to insert something to identify the range). Once I've identified the cells for the forumla I'm going to use another list from a seperate worksheet to grab a value from another list. The formula that I have been playing with is just for the grabbing of the value from the other list is: Range("L2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-2] C[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" It appears to work almost... for some reason it enters this equation into the worksheet: =VLOOKUP(65536:65536 E:E,'[Macro.xls]Revised Code List'!$A:$B,2,FALSE) I know I'm a novice but any help you can provide would be great. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Specifying cell(s) for Vlookup Function in Macro
Can you pick out a column to determine that last row?
Dim LastRow as long with worksheets("sheet9999") 'I used column A to get that last row lastrow = .cells(.rows.count,"A").end(xlup).row .range("l2:L" & lastrow).formular1c1 _ "=VLOOKUP(RC[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" end with I changed R[-2] to just R. I don't see how you're looking up something in row 0 since you're starting in row 2. Bill D. Toronto wrote: I'm trying to write a macro that will insert a column and formula for all cells in a list. The number of rows in the list will vary (so I have to insert something to identify the range). Once I've identified the cells for the forumla I'm going to use another list from a seperate worksheet to grab a value from another list. The formula that I have been playing with is just for the grabbing of the value from the other list is: Range("L2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-2] C[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" It appears to work almost... for some reason it enters this equation into the worksheet: =VLOOKUP(65536:65536 E:E,'[Macro.xls]Revised Code List'!$A:$B,2,FALSE) I know I'm a novice but any help you can provide would be great. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Specifying cell(s) for Vlookup Function in Macro
Hi Dave,
Thanks for getting to this so quick... and now you're going to see how green I am when it comes to macros and formula. :) I've copied in your code and I'm getting a runtime error '438'. This is the exact code that I have inserted (included are my small changes to point to the right document.) Dim LastRow As Long With Worksheets("NielsenExtract") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("l2:l" & LastRow).FormulaR1C1 _ "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)" Sorry for my ignorance and thanks a lot for your help! "Dave Peterson" wrote: Can you pick out a column to determine that last row? Dim LastRow as long with worksheets("sheet9999") 'I used column A to get that last row lastrow = .cells(.rows.count,"A").end(xlup).row .range("l2:L" & lastrow).formular1c1 _ "=VLOOKUP(RC[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" end with I changed R[-2] to just R. I don't see how you're looking up something in row 0 since you're starting in row 2. Bill D. Toronto wrote: I'm trying to write a macro that will insert a column and formula for all cells in a list. The number of rows in the list will vary (so I have to insert something to identify the range). Once I've identified the cells for the forumla I'm going to use another list from a seperate worksheet to grab a value from another list. The formula that I have been playing with is just for the grabbing of the value from the other list is: Range("L2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-2] C[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" It appears to work almost... for some reason it enters this equation into the worksheet: =VLOOKUP(65536:65536 E:E,'[Macro.xls]Revised Code List'!$A:$B,2,FALSE) I know I'm a novice but any help you can provide would be great. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Specifying cell(s) for Vlookup Function in Macro
Sorry, I dropped an equal sign:
.Range("l2:l" & LastRow).FormulaR1C1 _ = "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)" Bill D. Toronto wrote: Hi Dave, Thanks for getting to this so quick... and now you're going to see how green I am when it comes to macros and formula. :) I've copied in your code and I'm getting a runtime error '438'. This is the exact code that I have inserted (included are my small changes to point to the right document.) Dim LastRow As Long With Worksheets("NielsenExtract") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("l2:l" & LastRow).FormulaR1C1 _ "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)" Sorry for my ignorance and thanks a lot for your help! "Dave Peterson" wrote: Can you pick out a column to determine that last row? Dim LastRow as long with worksheets("sheet9999") 'I used column A to get that last row lastrow = .cells(.rows.count,"A").end(xlup).row .range("l2:L" & lastrow).formular1c1 _ "=VLOOKUP(RC[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" end with I changed R[-2] to just R. I don't see how you're looking up something in row 0 since you're starting in row 2. Bill D. Toronto wrote: I'm trying to write a macro that will insert a column and formula for all cells in a list. The number of rows in the list will vary (so I have to insert something to identify the range). Once I've identified the cells for the forumla I'm going to use another list from a seperate worksheet to grab a value from another list. The formula that I have been playing with is just for the grabbing of the value from the other list is: Range("L2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-2] C[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" It appears to work almost... for some reason it enters this equation into the worksheet: =VLOOKUP(65536:65536 E:E,'[Macro.xls]Revised Code List'!$A:$B,2,FALSE) I know I'm a novice but any help you can provide would be great. -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Specifying cell(s) for Vlookup Function in Macro
Thanks Dave! Worked like a charm. I bow to my new hero! ;)
"Dave Peterson" wrote: Sorry, I dropped an equal sign: .Range("l2:l" & LastRow).FormulaR1C1 _ = "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)" Bill D. Toronto wrote: Hi Dave, Thanks for getting to this so quick... and now you're going to see how green I am when it comes to macros and formula. :) I've copied in your code and I'm getting a runtime error '438'. This is the exact code that I have inserted (included are my small changes to point to the right document.) Dim LastRow As Long With Worksheets("NielsenExtract") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("l2:l" & LastRow).FormulaR1C1 _ "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)" Sorry for my ignorance and thanks a lot for your help! "Dave Peterson" wrote: Can you pick out a column to determine that last row? Dim LastRow as long with worksheets("sheet9999") 'I used column A to get that last row lastrow = .cells(.rows.count,"A").end(xlup).row .range("l2:L" & lastrow).formular1c1 _ "=VLOOKUP(RC[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" end with I changed R[-2] to just R. I don't see how you're looking up something in row 0 since you're starting in row 2. Bill D. Toronto wrote: I'm trying to write a macro that will insert a column and formula for all cells in a list. The number of rows in the list will vary (so I have to insert something to identify the range). Once I've identified the cells for the forumla I'm going to use another list from a seperate worksheet to grab a value from another list. The formula that I have been playing with is just for the grabbing of the value from the other list is: Range("L2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-2] C[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" It appears to work almost... for some reason it enters this equation into the worksheet: =VLOOKUP(65536:65536 E:E,'[Macro.xls]Revised Code List'!$A:$B,2,FALSE) I know I'm a novice but any help you can provide would be great. -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Specifying cell(s) for Vlookup Function in Macro
Glad you got it working.
And a real hero wouldn't have missed that equal sign <vbg. Bill D. Toronto wrote: Thanks Dave! Worked like a charm. I bow to my new hero! ;) "Dave Peterson" wrote: Sorry, I dropped an equal sign: .Range("l2:l" & LastRow).FormulaR1C1 _ = "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)" Bill D. Toronto wrote: Hi Dave, Thanks for getting to this so quick... and now you're going to see how green I am when it comes to macros and formula. :) I've copied in your code and I'm getting a runtime error '438'. This is the exact code that I have inserted (included are my small changes to point to the right document.) Dim LastRow As Long With Worksheets("NielsenExtract") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("l2:l" & LastRow).FormulaR1C1 _ "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)" Sorry for my ignorance and thanks a lot for your help! "Dave Peterson" wrote: Can you pick out a column to determine that last row? Dim LastRow as long with worksheets("sheet9999") 'I used column A to get that last row lastrow = .cells(.rows.count,"A").end(xlup).row .range("l2:L" & lastrow).formular1c1 _ "=VLOOKUP(RC[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" end with I changed R[-2] to just R. I don't see how you're looking up something in row 0 since you're starting in row 2. Bill D. Toronto wrote: I'm trying to write a macro that will insert a column and formula for all cells in a list. The number of rows in the list will vary (so I have to insert something to identify the range). Once I've identified the cells for the forumla I'm going to use another list from a seperate worksheet to grab a value from another list. The formula that I have been playing with is just for the grabbing of the value from the other list is: Range("L2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-2] C[-7],'[Macro.xls]Revised Code List'!C1:C2,2,FALSE)" It appears to work almost... for some reason it enters this equation into the worksheet: =VLOOKUP(65536:65536 E:E,'[Macro.xls]Revised Code List'!$A:$B,2,FALSE) I know I'm a novice but any help you can provide would be great. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using a cell to reference sheetname in vlookup function | Excel Worksheet Functions | |||
| Getting a cell address from Vlookup function in a macro? | New Users to Excel | |||
| Vlookup Function Arguments are correct but nothing in cell | Excel Worksheet Functions | |||
| Vlookup Function Cell shading | Excel Discussion (Misc queries) | |||
| Adding a Macro to a VLookup Function | Excel Discussion (Misc queries) | |||
Linear Mode
