Selecting and identifying ranges

[TheRedCardinal]

Hi all,

I have just opened, solved, and closed this thread:

https://www.mrexcel.com/forum/excel...ivot-tables-based-range-name.html#post5348104

But in it I have had to hard code a range, because I could not work out how to define it properly for my needs.

My situation is as follows:

I have 2 named ranges (1 cell each) called RepCountry and RepDirection. The user selects these values from drop down menus.

In my Data Sheet, I have a cell containing the formula = "P" & RepCountry & RepDirection

This gives me for example a name PUKOut.

Underneath this I have a table containing all the combinations of the combinations, and next to that 3, 4 or 5 columns with data in. The number varies by specific factors.

What I need to do is in my pivot table creation, pull in the required range for it to run through the "For Each Cell" loop in the row creation section.

I originally tried calling the range of data a specific name, for example cells G17:I17 named as "PUKOut" and then calling the cell with the formula in a range as well, "Pivot String"

So we have:

- a range called PivotString containing a formula which gives the name of the row to look at
- a series of ranges with the possible names for PivotString as their names

Then I tried to insert this into my VBA:

For Each CellA In Range("Pivotstring")

I quickly realised this wouldn't work as the Range PivotString consists of just one cell.

So then I tried:

For Each CellA In Range("Pivotstring").Value

It produced a pivot table with no rows.

When I stepped through the procedure, it only looped through the "For Each Cell" process once, which tells me that it is not identifying the extra cells.
However the "PivotString".Value function returns the correct name of the named range in the sheet.

So this I think is down to my repeated struggles with naming and referencing ranges.

Have I missed something fundamental?

Thanks!

 

[StephenCrump]

Based on a very quick read of your post, it looks like you want:

For Each CellA In Range("SomeValidRangeName")

where you have SomeValidRangeName stored in Range("Pivotstring").

So putting the two together, you'll need:

For Each CellA In Range(Range("Pivotstring").Value)