Creating a loop within a loop?

[hypeh]

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

[Bob Phillips]

Post your workbook mate, it will be more meaningful

[hypeh]

Unfortunately i am unable to as it contains sensitive date to the company. :(

however!!!! i have come up with another idea!

all i need is a for loop. I have worked out how to take the number of rows from the pivot table. so every time the pivot table is generated it will tell me how many rows are in it.

now i just need a for loop that happens for the number of rows found, and goes down each cell adding the formula. so in first cell it adds =(D3/D7)+E2 then in the second cell below it adds =(D4/D7)+E4" and so on until the for loop ends.

how can i do that? im very poor with loops i am afraid :( i can understand the concept of 1 but writing them i just cant seem to click with :(

many thanks in advance.

[Bob Phillips]

Why don't you just use the pivot table to do it, it can provide % of column.

[hypeh]

the pivot table collects the data from another location of data thats collected from a bespoke piece of software which was implemented about 15 years ago... and i cant change it.

[Bob Phillips]

You don't have to change the source. Surely you can change the pivot table?

[hypeh]

when the report is run, it automaticly generates the pivot tables. so if i change them. the next time it is ran, the pivot tables then regenerate off of the old data they were looking at. im not amazing at this to be entirely honest. but since the last guy left who created it, i been left to update it...

i have found a way to do it now... however can you tell me whats wrong with this?

ActiveCell.Formula = "=(RC[-1]/E66)+R[-1]C"

it doesnt seem to allow me to use E66 in the statement above. it puts ' ' around it once its copied into the cell....

if i try using a variable that contains the value of E66 it does the same to..

[Bob Phillips]

You need to use R1C1 reference, such as

R66C5

But it will probably be another cell some other time, so it will change.

Can you show me the Pivot generating code and I will see if I can do it in the pivot for you.

[hypeh]

here is the code for 1 of the pivot tables

Function Create_pvtAmendCustReq()

SourceSheetFullRef = "'[" & Format(Sheets("BreakdownCharts").cboWhichReport.Value, "YYYY-MM") & ".xls]OASDATA'!A:X"
PivotTableLocation = "'[" & ActiveWorkbook.Name & "]BreakdownCharts'!R96C2"

' Layout Pivot Table, define source ranges
ActiveWorkbook.Worksheets("BreakdownCharts").PivotTables("pvtAmendAllDept").PivotCache.CreatePivotTable TableDestination:=PivotTableLocation, TableName:="pvtAmendCustReq", _
DefaultVersion:=xlPivotTableVersion10
' Apply fields to table, removes blanks
ActiveSheet.PivotTables("pvtAmendCustReq").AddDataField ActiveSheet.PivotTables("pvtAmendCustReq").PivotFields("Order_No"), "Count of Order_No", xlCount
With ActiveSheet.PivotTables("pvtAmendCustReq").PivotFields("Primary_Reason_Desc")
.Orientation = xlRowField
.Position = 1
.PivotItems("Configurator Team").Visible = False
.PivotItems("Customer Request").Visible = True
.PivotItems("Order Processing").Visible = False
.PivotItems("Product Development").Visible = False
.PivotItems("Product Marketing").Visible = False
.PivotItems("Product Supply").Visible = False
.PivotItems("Production").Visible = False
.PivotItems("Sales").Visible = False
.PivotItems("System").Visible = False
End With
With ActiveSheet.PivotTables("pvtAmendCustReq").PivotFields("Secondary_Reason_Desc")
.Orientation = xlRowField
.Position = 2
.PivotItems("(blank)").Visible = False
End With