Results 1 to 9 of 9
Thread: Inserting formula in cell (2003)

20071015, 14:01 #1
 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

20071015, 14:07 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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

20071015, 14:10 #3
 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

20071015, 14:14 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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 = "=H5P5"

20071015, 14:21 #5
 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 H6P6 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

20071015, 14:26 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Inserting formula in cell (2003)
It's because the cell references in =H5P5 are relative, so Excel automatically adjusts them. The same happens interactively if you select Q5:Q121, type the formula =H5P5, 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.

20071015, 14:39 #7
 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

20071015, 18:52 #8
 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

20071015, 23:37 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Inserting formula in cell (2003)
But you did provide an efficient solution!