Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need code to loop through columns and enter formulas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need code to loop through columns and enter formulas
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need code to loop through columns and enter formulas
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 "joel" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need code to loop through columns and enter formulas
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! "Judy Ward" wrote: 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 "joel" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to enter if formulas | Excel Programming | |||
Enter 2 formulas in 1 cell | Excel Discussion (Misc queries) | |||
Formulas not filling in without F2+enter | Excel Programming | |||
Fastest way to enter many formulas | Excel Programming | |||
From worksheet enter DO-Loop start & end code | Excel Programming |