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