Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    78

    Unanswered: copy a formula only to the last 26 rows

    I did not design this macro and the designer is not available yet. Maybe you can help me.
    Currently the
    Cells(LastRow - 1, i + 1).FormulaR1C1 = "=RC[-1]/R[-13]C[-1]-1"
    is being copied to all rows.
    What line needs to be changed in order to copy this formula only to the last 26 rows?

    Thank you.

    Application.ScreenUpdating = False
    Dim LastColumn, LastRow As Integer
    Cells.MergeCells = False
    Rows("1:3").Delete Shift:=xlUp
    Columns("A:A").Delete Shift:=xlToLeft
    LastColumn = Range("IV1").End(xlToLeft).Column
    LastRow = Range("A65536").End(xlUp).Row + 1

    For i = 2 To (LastColumn + LastColumn - 1) Step 2
    Columns(i + 1).Insert
    Cells(1, i + 1).Value = "% of Grth"
    Columns(i + 1).NumberFormat = "0%"
    Columns(i + 1).ColumnWidth = 4.14
    Cells(LastRow - 1, i + 1).FormulaR1C1 = "=RC[-1]/R[-13]C[-1]-1"
    Cells(LastRow - 1, i + 1).Select
    Selection.AutoFill Destination:=Range(Cells(2, i + 1), Cells(LastRow - 1, i + 1)), Type:=xlFillDefault
    Next i

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Joe

    This maybe what you want
    Code:
    If LastRow > 25 Then
            For i = 2 To (LastColumn * 2 - 1) Step 2
                If LastColumn > 0 Then
                    Columns(i + 1).Insert
                    Cells(1, i + 1).Value = "% of Grth"
                    Columns(i + 1).NumberFormat = "0%"
                    Columns(i + 1).ColumnWidth = 4.14
                    Cells(LastRow - 1, i + 1).FormulaR1C1 = "=RC[-1]/R[-13]C[-1]-1"
                    Cells(LastRow - 1, i + 1).Select
                    Selection.AutoFill Destination:=Range(Cells(LastRow - 25, i + 1), Cells(LastRow - 1, i + 1)), Type:=xlFillDefault
                End If
            Next i
        End If
    ie change

    Selection.AutoFill Destination:=Range(Cells(2, i + 1), Cells(LastRow - 1, i + 1)), Type:=xlFillDefault

    to
    Selection.AutoFill Destination:=Range(Cells(LastRow - 25, i + 1), Cells(LastRow - 1, i + 1)), Type:=xlFillDefault


    Is that it ??


    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •