Thread: Inserting formula in cell (2003)

1. 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?

Ken

2. 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. 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. 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. 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. 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. 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. 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. 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
•