VBA for lookup when no. of rows changes?

[1021557]

Hi, I've written this macro to add a lookup formula in column Z if there is a value in column A and it works fine for this month. The only problem is it will only cover as far as Z 681 as that's how many rows are in column A this month but next month it might be 600 or 847, I simply don't know. How can I get the formula to copy down in column Z depending on the number of values in column A each month? Some sort of an If statement maybe?

Here's the code:

Range("Z2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-25]<>"""",VLOOKUP(RC[-25],'Lookup sheet'!C[-25]:C[-22],4,FALSE))"
    Range("Z2").Select
    Selection.AutoFill Destination:=Range("Z2:Z681")
    Range("Z2:Z681").Select
    Columns("Z:Z").Select
    Selection.NumberFormat = "mmm-yy"

Thanks

Mike

 

[Fluff]

Try

   With Range("Z2", Range("A" & Rows.Count).End(xlUp).Offset(, 25))
      .FormulaR1C1 = _
         "=IF(RC[-25]<>"""",VLOOKUP(RC[-25],'Lookup sheet'!C[-25]:C[-22],4,FALSE))"
      .NumberFormat = "mmm-yy"
   End With