Stop Macro

[JimmyA]

Hi I have a macro which is copying and pasting a vlookup.

How do I get it top running hen the data the spreadsheet runs out? instead of giving it an line number to end on as below:

Sub vlookup()
'
' vlookup Macro
'
'
Range("N2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Lookup!C[-9]:C[-6],4,FALSE)"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Lookup!C[-11]:C[-9],3,FALSE)"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],Lookup!C[-12]:C[-6],7,FALSE)"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],Lookup!C[-13]:C[-6],8,FALSE)"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-18],Lookup!C[-14]:C[-6],9,FALSE)"
Range("N2:S2").Select
Selection.Copy
Range("N3:N6000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("N2").Select
Columns("N:N").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Range("T2").Select
Columns("Q:Q").EntireColumn.AutoFit
Range("U2").Select
Columns("R:R").EntireColumn.AutoFit
Columns("S:S").EntireColumn.AutoFit
Range("N1").Select
End Sub



Thanks Jimmy

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub vlookupmacro()
   Dim Lr As Long
   
   Lr = Range("A" & Rows.Count).End(xlUp).row
   Range("N2:N" & Lr).FormulaR1C1 = "=VLOOKUP(RC[-13],Lookup!C[-9]:C[-6],4,FALSE)"
   Range("P2:P" & Lr).FormulaR1C1 = "=VLOOKUP(RC[-15],Lookup!C[-11]:C[-9],3,FALSE)"
   Range("Q2:Q" & Lr).FormulaR1C1 = "=VLOOKUP(RC[-16],Lookup!C[-12]:C[-6],7,FALSE)"
   Range("R2:R" & Lr).FormulaR1C1 = "=VLOOKUP(RC[-17],Lookup!C[-13]:C[-6],8,FALSE)"
   Range("S2:S&lr").FormulaR1C1 = "=VLOOKUP(RC[-18],Lookup!C[-14]:C[-6],9,FALSE)"
   Columns("N:N").EntireColumn.AutoFit
   Columns("P:P").EntireColumn.AutoFit
   Columns("Q:Q").EntireColumn.AutoFit
   Columns("R:R").EntireColumn.AutoFit
   Columns("S:S").EntireColumn.AutoFit
   Range("N1").Select
End Sub

 

[JimmyA]

Thanks a lot for that, it's worked perfectly.

Cheers

Jimmy

 

[Fluff]

Glad to help & thanks for the feedback