Is there data in columm M? if so then your approach will work and you will
overwrite that data with the formula. If there isn't, then you will write
your formula in M1:M2. Perhaps you should be using another column to
determine the last row. (then change "M65536" to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need to
do. I have a File that varies in length from being Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9 VLOOKUP
formulas. I need to find the Last row using a Macro (Ctrl-
Down-Arrow, or Ctrl-End), and reference that number in my
formulas (see below, replacing M65536 with the Last Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row number in
the document. This will save enormous amounts of time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my exact
needs. I appreciate all those individuals who have gotten
me this far.

Any and all assistance will be greatly appreciated.