Copy a formula in excel to the last row of data 1

[julesl]

Hi

I've created the macro below using VBA in excel and I'm trying to get the formula to copy to the last row of data within the spreadsheet rather than to a specific point.

Sub CopyForm()
'
Range("N1").Select
ActiveCell.FormulaR1C1 = "RR Termination Date"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC2), MONTH(RC2)+3, DAY(RC2))"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N100")
Range("N2:N100").Select
End Sub

Can anyone tell me how I can do this please?

Thanks

Julie

 

[Loomah]

Hi Julie
Have a look in the FAQ section as there are at least 2 on how to find the last used row.

You could then assign the last row to a variable and use that variable to identify your destination range.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

Hi again
I've just seen your other post where you say you don't really know VBA so I thought I'd expand on my prevous post.

The following should do what you need. Note that there is no need to select the cells involved here. If you are just learning VBA and recording macros (probably the best way to start) this is one of the things you could look at and remove from what the recorder gives you.

Sub a()
Dim lRow As Long
lRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
With Range("N2")
    .FormulaR1C1 = "=DATE(YEAR(RC2), MONTH(RC2)+3, DAY(RC2))"
    .AutoFill Destination:=Range("N2:N" & lRow)
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[julesl]

Great, it works perfectly.

Thanks very much for your help

Jules