Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Inserting formula in cell (2003)

    I am trying to populate a column of 100+ cells with the same formula using VBA. However, the only luck I am having is bad luck.

    My most recent attempt is:
    For i = 5 To 121
    xlApp.Range("Q" & i).Select
    CellFormula = "=H" & i & "-P" & i
    xlApp.ActiveCell.FormulaR1C1 = CellFormula '"H" & i "-P" & i '
    Next i

    Is there a way to make this work? Is there a better approach?

    thanks in advance for your assistance.

    Ken

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

    Re: Inserting formula in cell (2003)

    You're using FormulaR1C1 to set a formula that uses A1 notation. This naturally confuses Excel.
    You should either use FormulaR1C1 and R1C1 notation, or use Formula and A1 notation.
    BTW, it isn't necessary to select a cell to do something with it. You can set the formula of a range object:

    For i = 5 To 121
    xlApp.Range("Q" & i).Formula = "=H" & i & "-P" & i
    Next i

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting formula in cell (2003)

    Not sure exactly what you are after but does this work:

    Range("Q5:Q121").Formula = "=H5 & P5"

    Steve

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

    Re: Inserting formula in cell (2003)

    Ken is using Automation, so xlApp is essential. And there is a minus sign in his formula, so your example should probably be

    xlApp.Range("Q5:Q121").Formula = "=H5-P5"

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Inserting formula in cell (2003)

    Thank you Steve and Hans!

    Hans, as usual your solution worked.

    May I ask why it was not necessary to tell Excel to increment the row? How does it know to use H6-P6 for the sixth row? I would thought it would try to subract the same pair of cells everytime.

    I hate to be so ignorant.

    Thanks.

    Ken

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

    Re: Inserting formula in cell (2003)

    It's because the cell references in =H5-P5 are relative, so Excel automatically adjusts them. The same happens interactively if you select Q5:Q121, type the formula =H5-P5, then press Ctrl+Enter to fill the entire selection.

    If you had used =$H$5-$P$5, the formula would have been the same in all cells.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Inserting formula in cell (2003)

    AH! So the $ forces it.

    THANK YOU SO MUCH! Not only for the solution but the explanation as well.

    Ken

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting formula in cell (2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>I didn't read the code well enough...

    Steve

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

    Re: Inserting formula in cell (2003)

    But you did provide an efficient solution!

Posting Permissions

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