hypeh
09-16-2010, 02:41 AM
Hi guys,
I am working on a reporting system for work, however it was already created before by a previous employee who left.
Basicly the document contains pivot tables, each time the report is ran, the pivot tables update and sometimes they may consist of more or less rows depending on the data which is collected from another location.
my objective is to write some code that will take the first (e.g. D6) cell next to the table of data already there, then calculate the % and then loop round adding the % next to each cell of data in the table... the issue I have is, each time the cell shifts down to add the formula for the % it will change, I need it to be like it is below:
first cell: (this is E3)
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=(D3/D7)+E2"
second cell:
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=(D4/D7)+E3"
third cell:
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=(D5/D7)+E4"
and I need this to be a continuous loop inside a do while loop. the do while loop will continue to loop until column B equals the value of "Grand Total".
the current pivot table looks like below:
A B C D E
1 Count of Order_No
2 Primary_Reason_Desc Secondary_Reason_Desc Total
3 Configurator Team Incorrect Advice 1
4 Incorrect BOM Item 5
5 Incorrect Option Given 1
6 Configurator Team Total 7
7 Grand Total 7
so the % is calculated in column E.
as i said before, since the pivot table updates and can increase in rows or decrease, I need the loops to check where grand total is first, so i can tell the loop to end, and only fill the cells next to the total column... if this makes sense or maybe you have an easier idea?
many thanks in advance,
regards,
Alan
I am working on a reporting system for work, however it was already created before by a previous employee who left.
Basicly the document contains pivot tables, each time the report is ran, the pivot tables update and sometimes they may consist of more or less rows depending on the data which is collected from another location.
my objective is to write some code that will take the first (e.g. D6) cell next to the table of data already there, then calculate the % and then loop round adding the % next to each cell of data in the table... the issue I have is, each time the cell shifts down to add the formula for the % it will change, I need it to be like it is below:
first cell: (this is E3)
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=(D3/D7)+E2"
second cell:
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=(D4/D7)+E3"
third cell:
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=(D5/D7)+E4"
and I need this to be a continuous loop inside a do while loop. the do while loop will continue to loop until column B equals the value of "Grand Total".
the current pivot table looks like below:
A B C D E
1 Count of Order_No
2 Primary_Reason_Desc Secondary_Reason_Desc Total
3 Configurator Team Incorrect Advice 1
4 Incorrect BOM Item 5
5 Incorrect Option Given 1
6 Configurator Team Total 7
7 Grand Total 7
so the % is calculated in column E.
as i said before, since the pivot table updates and can increase in rows or decrease, I need the loops to check where grand total is first, so i can tell the loop to end, and only fill the cells next to the total column... if this makes sense or maybe you have an easier idea?
many thanks in advance,
regards,
Alan