Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RE: Error on copying a formula value (Excel 2000)

    I have the following code:

    Range("b31").Select
    ActiveCell.FormulaR1C1 = "=R3C7+R3C8*RC[2]/100+R3C9*R[-1]C+R3C10"
    Selection.AutoFill Destination:=Range("b31"), Type:=xlFillDefault
    Range("b31").Select
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThick
    .ColorIndex = xlAutomatic
    End With

    I receive the error message Run-time error 1004
    Autofill method of range class failed.

    Whereas this one works:
    Range("b31").Select
    ActiveCell.FormulaR1C1 = "=R3C7+R3C8*RC[2]/100+R3C9*R[-1]C+R3C10"
    Range("b31").Select
    Selection.AutoFill Destination:=Range("b31:b36"), Type:=xlFillDefault
    Range("b31:b36").Select
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThick
    .ColorIndex = xlAutomatic
    End With

    Can anyone help? Thanks.

    F

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: RE: Error on copying a formula value (Excel 2000)

    AutoFill must be applied to a range of more than one cell, otherwise it doesn't make sense. In the first piece of code, the destination (target) for the AutoFill is only cell B31:

    ..., Destination:=Range("b31"), ...

    Hence it fails. In the second piece of code, the destination is the range B31:B36:

    ..., Destination:=Range("b31:b36"), ...

    and that is OK.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Error on copying a formula value (Excel 2000)

    Thanks for that. I've also tried range("b31:b31") and that fails . How can get the value into b31 without using autofill? This is a fuller spec of the code:
    Range("b31").Select
    ActiveCell.FormulaR1C1 = "=R3C7+R3C8*RC[2]/100+R3C9*R[-1]C+R3C10"
    Range("b31").Select
    Selection.AutoFill Destination:=Range("b31"), Type:=xlFillDefault
    Range("b31").Select
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThick
    .ColorIndex = xlAutomatic
    End With

    I' like a separate formula for b31 i.e.

    ActiveCell.FormulaR1C1 = "=R3C7+R3C8*RC[2]/100+R3C9*R[-1]C[1]+R3C10"
    and
    ActiveCell.FormulaR1C1 = "=R3C7+R3C8*RC[2]/100+R3C9*R[-1]C+R3C10"

    for the other cells, that's why i've isolated b31.

    Thanks.

    Farmer

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: RE: Error on copying a formula value (Excel 2000)

    If you want separate formulas, you shouldn't use AutoFill for B31.
    Set the formula for B31.
    Then set a different formula for B32 and use AutoFill to propagate it to B33:B36.

    Range("B31").FormulaR1C1 = "=R3C7+R3C8*RC[2]/100+R3C9*R[-1]C[1]+R3C10"
    Range("B32").FormulaR1C1 = "=R3C7+R3C8*RC[2]/100+R3C9*R[-1]C+R3C10"
    Range("B32").AutoFill Destination:=Range("B32:B36"), Type:=xlFillDefault
    With Range("B31").Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThick
    .ColorIndex = xlAutomatic
    End With

    Note that iti isn't necessary to select cells, you can apply the code to the ranges directly instead of selecting them and applying the code to the selection.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: RE: Error on copying a formula value (Excel 2000)

    Addition: instead of setting the formula for B32 and filling down, you can also set the formula for B32:B36 in one go:

    Range("B32:B36").FormulaR1C1 = "=R3C7+R3C8*RC[2]/100+R3C9*R[-1]C+R3C10"

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Error on copying a formula value (Excel 2000)

    Many thanks for your help which is much appreciated.

    Farmer

Posting Permissions

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