Need code to loop through columns and enter formulas

[Judy Ward]

I know it is possible to do this in code, but I just can't figure it out.
I'm hoping there is an Excel wizard out there who can help me with this.

I'm starting with Dates in row 1 and counts related to the dates in row 2.
Example:
C D E
2/27/09 3/6/09 3/13/09
1 6 4

I need "burndown" calculations based on the counts in row 2. Since I will
be creating a chart with this data, I want the burndown numbers next to the
dates, so I insert a blank row 2 (the counts become row 3). I can figure out
how to put a total of all the counts in row 3 into cell B2. And I know how
to get the number of columns. Now I need to know how to loop through the
rest of the cells in row 2 (from column C to the last column, which is
variable).

This is what I want to end up with:
C2 = B2-C3
D2 = C2-D3
E2 = D2-E3
and so on until the last column.

Thank you in advance for any help you can give me!
Judy

 

[joel]

Your last c olun can be determine by the data in Row 1

LastCol = Cells(1,columns.count).end(xltoleft).Column
'Column C = 3
For colcount = 3 to LastCol
Cells(2,ColCount).formulaR1C1 = "RC[-1]-R[1]C"
Next ColCount

 

[Judy Ward]

Joel,

Thank you for responding. The code sample you gave me isn't quite working.
It does populate the right columns in row 2, but what is appearing in each
cell is "RC[-1]-R[1]C" and not the value I was hoping for. I tried simply
adding a "=" in front of the formula, and Excel was not happy with that.

Can you think of anything else I can try?

Thank you,
Judy

Your last c olun can be determine by the data in Row 1

LastCol = Cells(1,columns.count).end(xltoleft).Column
'Column C = 3
For colcount = 3 to LastCol
Cells(2,ColCount).formulaR1C1 = "RC[-1]-R[1]C"
Next ColCount

I know it is possible to do this in code, but I just can't figure it out.
I'm hoping there is an Excel wizard out there who can help me with this.

I'm starting with Dates in row 1 and counts related to the dates in row 2.
Example:
C D E
2/27/09 3/6/09 3/13/09
1 6 4

I need "burndown" calculations based on the counts in row 2. Since I will
be creating a chart with this data, I want the burndown numbers next to the
dates, so I insert a blank row 2 (the counts become row 3). I can figure out
how to put a total of all the counts in row 3 into cell B2. And I know how
to get the number of columns. Now I need to know how to loop through the
rest of the cells in row 2 (from column C to the last column, which is
variable).

This is what I want to end up with:
C2 = B2-C3
D2 = C2-D3
E2 = D2-E3
and so on until the last column.

Thank you in advance for any help you can give me!
Judy

 

[Judy Ward]

Happy Day, before my last post I just tried entering the "=" in front of the
formula in the Excel sheet and got an error. I just tried putting the "=" in
the code below and it worked beautifully: Cells(2,ColCount).formulaR1C1 =
"=RC[-1]-R[1]C"

I will have a MUCH better day now. Thank you very much!

Joel,

Thank you for responding. The code sample you gave me isn't quite working.
It does populate the right columns in row 2, but what is appearing in each
cell is "RC[-1]-R[1]C" and not the value I was hoping for. I tried simply
adding a "=" in front of the formula, and Excel was not happy with that.

Can you think of anything else I can try?

Thank you,
Judy

Your last c olun can be determine by the data in Row 1

LastCol = Cells(1,columns.count).end(xltoleft).Column
'Column C = 3
For colcount = 3 to LastCol
Cells(2,ColCount).formulaR1C1 = "RC[-1]-R[1]C"
Next ColCount

I know it is possible to do this in code, but I just can't figure it out.
I'm hoping there is an Excel wizard out there who can help me with this.

I'm starting with Dates in row 1 and counts related to the dates in row 2.
Example:
C D E
2/27/09 3/6/09 3/13/09
1 6 4

I need "burndown" calculations based on the counts in row 2. Since I will
be creating a chart with this data, I want the burndown numbers next to the
dates, so I insert a blank row 2 (the counts become row 3). I can figure out
how to put a total of all the counts in row 3 into cell B2. And I know how
to get the number of columns. Now I need to know how to loop through the
rest of the cells in row 2 (from column C to the last column, which is
variable).

This is what I want to end up with:
C2 = B2-C3
D2 = C2-D3
E2 = D2-E3
and so on until the last column.

Thank you in advance for any help you can give me!
Judy