Marcro to copy and paste a formula

[Jodie]

I want to write a macro that copies and pastes a formula down column A, but I
want it to stop where column B equals "Grand Total". Can anyone help with
this?

 

[Jacob Skaria]

Try

lngRow = Columns(2).Find("Grand Total").Row - 1
Range("A1:A" & lngRow).Formula = "=B1+C1"

If this post helps click Yes

 

[Mike H]

Jodie,

It would have helped if you told us what formula but this should point you
in the right direction

Sub Sonic()
r = Cells.Find(What:="Grand total", After:=Range("B1"),
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Range("a1").Formula = "=B1"
Range("A1:A" & r).FillDown
Range("A5").Select
End Sub

Mike

 

[Jodie]

Sorry Mike, this is what I was using. I didn't know how to make it end at
"Grand Total".

Sub InsertPLANIDinCOL()
'
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[1]"
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-1]C[1]=""Grand Total"","""",IF(RC[1]="""",R[-1]C,RC[1]))"
Range("A2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End Sub

 

[Jodie]

Thank you Jacob, this worked for me.

Mike, I will try yours out as well to see which works best for my project.