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?
--
Thank you, Jodie

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Jodie" wrote:

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?
--
Thank you, Jodie

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" wrote:

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?
--
Thank you, 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

--
Thank you, Jodie


"Mike H" wrote:

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" wrote:

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?
--
Thank you, Jodie

Thank you Jacob, this worked for me.

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


"Jacob Skaria" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Jodie" wrote:

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?
--
Thank you, Jodie