Specifying cell(s) for Vlookup Function in Macro

[Guest]

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]

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.

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.

 

[Guest]

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!

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.

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]

Sorry, I dropped an equal sign:

.Range("l2:l" & LastRow).FormulaR1C1 _
= "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)"

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!

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.

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.

 

[Guest]

Thanks Dave! Worked like a charm. I bow to my new hero!

Sorry, I dropped an equal sign:

.Range("l2:l" & LastRow).FormulaR1C1 _
= "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)"

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!

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]

Glad you got it working.

And a real hero wouldn't have missed that equal sign <vbg>.

Thanks Dave! Worked like a charm. I bow to my new hero!

Sorry, I dropped an equal sign:

.Range("l2:l" & LastRow).FormulaR1C1 _
= "=VLOOKUP(RC[-7],'[Nielsen Macro.xls]Revised Code List'!C1:C2,2,FALSE)"

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!

:

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.