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

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

> 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

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

"Ronbo" wrote:

> 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

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

> 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

Perfect! Thanks a lot.

"Joel" wrote:

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

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

On Sat, 28 Jun 2008 10:08:00 -0700, Ronbo <(E-Mail Removed)>
wrote:

>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