Help with a Macro

Posted by JPS on February 03, 2001 4:16 PM

how do you copy a fomula down a column of cells and have it end to the last cell of my sheet. Like if my sheet goes to A1 to A10 one day and A1 to A20 the next day. I need to to this often so would like to make a macro for it.

Posted by Celia on February 03, 2001 6:08 PM

JPS
To put your formula in the range from A1 to the last cell in column A of the sheet's used range :-
Sub Fill_Formula()
Dim LastRow As Long
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
Range("A1:A" & LastRow).FormulaR1C1 = "Your formula in R1C1 format"
End Sub

Alternatively, if your formula is always already entered in A1, you can copy it down to the last cell with :-
Sub Copy_Formula()
Dim LastRow As Long
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
Range("A1").Copy
Range("A2:A" & LastRow).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub

Celia

Posted by JPS on February 03, 2001 6:28 PM

Thanks Celia,
I will give it a try and let you know if it works for me.
Many Thanks!
JPS

Posted by JPS on February 03, 2001 6:47 PM

Celia,
Works Great!!!!!!

Thank you Very Much!
JPS