Copy Fomulas down

[Ronbo]

I have data in columns A:E and corresponding formulas in F:G. Data is added
to columns A:E and I am trying to create a formula that will find the last
formula(s) in columns F:G and copy it down to the end of the data.

I have found numours ways to do this using a static reference in F & G, but
I have to use the last formula in the column to copy down.

Any help is appreciated.

Ronbo

 

[FSt1]

hi
formulas return values, they cannot perform actions like copy and paste. you
will have to do this manuallly or via macro.

Regards
FSt1

 

[Ronbo]

P.S. I am trying to create a routine/macro, not a formula.

 

[Joel]

Sub copyformulas()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastRowF = 0
LastRowG = 0
For RowCount = 1 To LastRow

If Left(Range("F" & RowCount).Formula, 1) = "=" Then
LastRowF = RowCount
End If
If Left(Range("G" & RowCount).Formula, 1) = "=" Then
LastRowG = RowCount
End If

Next RowCount

Range("F" & LastRowF).Copy _
Destination:=Range("F" & (LastRowF + 1) & ":F" & _
LastRow)
Range("G" & LastRowG).Copy _
Destination:=Range("G" & (LastRowG + 1) & ":G" & _
LastRow)
End Sub

 

[Ronbo]

Perfect! Thanks a lot.

Sub copyformulas()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastRowF = 0
LastRowG = 0
For RowCount = 1 To LastRow

If Left(Range("F" & RowCount).Formula, 1) = "=" Then
LastRowF = RowCount
End If
If Left(Range("G" & RowCount).Formula, 1) = "=" Then
LastRowG = RowCount
End If

Next RowCount

Range("F" & LastRowF).Copy _
Destination:=Range("F" & (LastRowF + 1) & ":F" & _
LastRow)
Range("G" & LastRowG).Copy _
Destination:=Range("G" & (LastRowG + 1) & ":G" & _
LastRow)
End Sub

 

[Gord Dibben]

Sub Auto_Fill()
Dim Elastrow As Long
Dim Flastrow As Long
With ActiveSheet
Flastrow = Range("F" & Rows.Count).End(xlUp).Row
Elastrow = Range("E" & Rows.Count).End(xlUp).Row
Range("F" & Flastrow & ":G" & Elastrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP