Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Copying formula in variable length column

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to copy a formula in a variable length column via a macro.
    I am using the following:
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
    Selection.AutoFill Destination:=Range("I2:I6264"), Type:=xlFillDefault
    Range("I2:I6264").Select
    Obviously, this is not acceptable for variable lengths.
    What can I do to accomplish?
    Thanks,

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code:

    Rowcounter = Intersect(ActiveSheet.UsedRange, Columns("I")).Rows.Count
    For i = 2 To Rowcounter
    Range("I" & i).FormulaR1C1 = "=RC[-1]-RC[-2]"
    Next

    The code places your formula in all cells from I2 to the end of the column.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-19 12:25, Al Chara wrote:
    Try the following code:

    Rowcounter = Intersect(ActiveSheet.UsedRange, Columns("I")).Rows.Count
    For i = 2 To Rowcounter
    Range("I" & i).FormulaR1C1 = "=RC[-1]-RC[-2]"
    Next

    The code places your formula in all cells from I2 to the end of the column.
    Ouch... that loop just hurt me !

    Try this one:

    Range("I2").Resize(Rowcounter,1).FormulaR1C1 = "=RC[-1]-RC[-2]"

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just getting in on the act, this might work also, and it will handle non-contiguous data in column H (which you could adjust for G which if you do, change the offset column reference from 1 to 2.).

    Sub FormulaFun()
    Range([H2], [H65536].End(xlUp)).Offset(0, 1).Formula = "=RC[-1]-RC[-2]"
    End Sub



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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