I have a data set that comes from an access database. I plan to dump the output into Excel each month, then use VBA to create a report based on it. To do this, I need to define:
For example, the sectors will be something like:
and each sector has subsectors under it, like
Both sectors and subsectors are dynamic, so I need a way to create my sector named ranges that is dynamic. I thought about using a pivot table, but I need to do some complex calculations on the data, which I don't believe I can support via calculated fields (I am not presuming it can't be done, but I haven't been able to figure it out).
I have a macro which will create the sector names starting in cell F5
It then creates a unique subsector list under each sector.
The last step I need is to create named ranges for each of the sectors. The named ranges should use the sector name as the named range name. It is fine if it puts underscores where the spaces are in the sectors, I can code around that later. My code looks like this:
Dim rng As Range
Dim c As Range
'sets sector range
Set rng = Range("F5:IV5")
'loops each cell and creates named range
For Each c In rng
'checks if there is a value in the cell (if not, I don't want a range created)
If Len(c.Value) > 0 Then
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
The code creates the first named range, but then stops. Also, it pastes F5:IV5 in D5:IT5.
Does anyone know why this is happening?