how to combine 2 loops? one uses a pivot table...

[jbaich]

Hi everybody, I have 2 macros that both work perfectly on their own. Each macro is a loop that runs off a different worksheet, so for example Macro 1 (loop 1) runs on sheet 1 and macro 2 (loop 2) runs on sheet 2. I would like to combine these so that rather than loop 2 just looping through all of the data in worksheet 2, it performs it's operations based on the current value that loop one is working on. So basically what I'm trying to do is tell loop 2 to find the loop 1 value in sheet 2 and perform it's functions on that cell. I'm thinking that this will mean loop 2 no longer needs to be a loop as it would just become part of loop 1?

Worksheet 1 data is just regular rows and columns, worksheet 2 data is a pivot table.

The loop section of the code for loop 1 is ...

  For i = 2 To LRow '//Loop through each row\\

    Roll = Master.Sheets("DO NOT DELETE").Range("C" & i).Value
'// so the macro performs its instructions for each value in column C\\

The loop section for loop 2 is...

  Set CIDpis = .PivotFields("A-J-R").VisibleItems  'PivotItems
  For Each D In CIDpis

I'm trying to replace [For Each D in CIDpis] with the equivalent of "D=the range containing the value (Roll) that loop 1 is currently processing", but I haven't been able to get it to work... I've been able to pass the value from loop one, but I think I need to set "D" to equal the cell reference (or address?) of that value in the pivot table or something. This seems like it should be really easy and probably is, but I'm still pretty new to VBA and have not been able to figure out how to set "D" to the location in the pivot table that contains the value = to "Roll"

is this an index and/or match based solution I'm looking for? or intersect? or the found property? cell address?

In case it's helpful, the next part of loop 2 that relies on "D" is...

    arecount = 0
    For Each are In D.LabelRange.Areas
      arecount = arecount + 1
      If Not IsEmpty(are.Cells(1).Offset(, 1)) Then  'a new Building iD.
        If Not IsEmpty(SumRangeStartRow) Then
          If arecount <> 1 Then Summary SumRangeStartRow, SumRangeEndRow
          Set destn = destn.Offset(3)

I should also mention that I received A LOT of help with this code from another member and I don't totally understand it... as I have virtually no experience with pivot tables.

Anyways, that's about it... I've been working on this project for a long time and I think this is the last hurdle to overcome.

As always, you assistance is very much appreciated!

Thanks,
Joe
__________________________________________________________________

Mod edit : post moved to appropriate forum​

 

[Stevie]

Please attach an example file, or give a visual example of what you envisage.

 

[jbaich]

Hi Stevie, another member who had been helping me previously has graciously offered to take a look as well, so in order not to duplicate effort and possibly waste anyone's time, I'll see if we can find a solution, but in the event that we can't, I will try to post a file or visual example as you've suggested.

Thanks,
Joe