Good evening,
Background:
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:
Sectors
Subsectors
For example, the sectors will be something like:
Aaa Rated
Aa Rated
A Rated
Baa Rated
Ba Rated
B Rated
Ca Rated
NR
and each sector has subsectors under it, like
BTAS
HUWHY
GOOG
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:
Sub NameSectors()
Dim rng As Range
Dim c As Range
ThisWorkbook.Activate
Sheets("Decode").Activate
'sets sector range
Set rng = Range("F5:IV5")
'loops each cell and creates named range
For Each c In rng
c.Select
'checks if there is a value in the cell (if not, I don't want a range created)
If Len(c.Value) > 0 Then
Range(Selection, Selection.End(xlDown)).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
False
End If
Next c
End Sub
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?
Thank you,
Jeremy