range naming not working in VBA
 

I'm having a strange problem hopefully one of the VBA gurus can figure
out. The code snippet below takes a list of headers (lines 10-14),
formats them (lines 32-45), then creates a named range using the
header name.

My problem is that as range 1 is created, the range is correctly
located starting at the uppermost left hand cell (the header label)
continuing down one row and over 7 columns (lines 25-26). However, as
each following range is created, the preceding ranges are reset to
whatever the latest range addresses are. If the range name listbox is
activated, all the ranges are listed, but they all refer to the last
created range, even though they show with unique range addresses as
they are created.

Art


1 Option Base 1
2 ----------------------------------------
3 Sub header_creation_routine()
4
5 Dim GroupTitles As Variant, i As Integer, TitleCount
As Integer
6
7 Worksheets("summary").Activate
8
9 'list headers in this section
10 GroupTitles = Array("Header_1", _
11 "Header_2", _
12 "Header_3", _
13 "Header_4", _
14 "Header_5")
15
16 TitleCount = UBound(GroupTitles) ' determines the
number of groups
17
18 ReDim Preserve GroupTitles(TitleCount)
19
20 Range("A4").Select ' move to starting
position
21 For i = 1 To TitleCount
22 ActiveCell = GroupTitles(i)
23 format_headers ' header
formatting subroutine
24 ActiveCell.Range("A1").Select
25 ActiveWorkbook.Names.Add Name:=GroupTitles(i),
RefersTo:= _
26 "='sheet1'!a1:h2"
27 ActiveCell.Offset(3, 0).Range("A1").Select ' jump a
few lines between headers
28 Next i
29
30 End Sub
31
----------------------------------------------------------------
32 Sub format_headers()
33 ' format section labels
34 With ActiveCell.Characters.Font
35 .Name = "Arial"
36 .Bold = True
37 .Italic = True
38 .Size = 10
39 .ColorIndex = 2
40 End With
41 With Selection.Interior
42 .ColorIndex = 5
43 .Pattern = xlSolid
44 End With
45 End Sub

range naming not working in VBA
 

The problem is that you are creating Range Names with Relative references in
the RefersTo formula.
In general its better to only create them with absolute references
'sheet1'!$a$1:$h$2

If you really need relative references in Names I recommend using R1C1
reference mode rather than A1 mode, its easier to see what you are doing.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"c1802362" wrote in message
...
I'm having a strange problem hopefully one of the VBA gurus can figure
out. The code snippet below takes a list of headers (lines 10-14),
formats them (lines 32-45), then creates a named range using the
header name.

My problem is that as range 1 is created, the range is correctly
located starting at the uppermost left hand cell (the header label)
continuing down one row and over 7 columns (lines 25-26). However, as
each following range is created, the preceding ranges are reset to
whatever the latest range addresses are. If the range name listbox is
activated, all the ranges are listed, but they all refer to the last
created range, even though they show with unique range addresses as
they are created.

Art


1 Option Base 1
2 ----------------------------------------
3 Sub header_creation_routine()
4
5 Dim GroupTitles As Variant, i As Integer, TitleCount
As Integer
6
7 Worksheets("summary").Activate
8
9 'list headers in this section
10 GroupTitles = Array("Header_1", _
11 "Header_2", _
12 "Header_3", _
13 "Header_4", _
14 "Header_5")
15
16 TitleCount = UBound(GroupTitles) ' determines the
number of groups
17
18 ReDim Preserve GroupTitles(TitleCount)
19
20 Range("A4").Select ' move to starting
position
21 For i = 1 To TitleCount
22 ActiveCell = GroupTitles(i)
23 format_headers ' header
formatting subroutine
24 ActiveCell.Range("A1").Select
25 ActiveWorkbook.Names.Add Name:=GroupTitles(i),
RefersTo:= _
26 "='sheet1'!a1:h2"
27 ActiveCell.Offset(3, 0).Range("A1").Select ' jump a
few lines between headers
28 Next i
29
30 End Sub
31
----------------------------------------------------------------
32 Sub format_headers()
33 ' format section labels
34 With ActiveCell.Characters.Font
35 .Name = "Arial"
36 .Bold = True
37 .Italic = True
38 .Size = 10
39 .ColorIndex = 2
40 End With
41 With Selection.Interior
42 .ColorIndex = 5
43 .Pattern = xlSolid
44 End With
45 End Sub

range naming not working in VBA
 

On Nov 6, 3:08*am, "Charles Williams"
wrote:
The problem is that you are creating Range Names with Relative references in
the RefersTo formula.
In general its better to only create them with absolute references
'sheet1'!$a$1:$h$2

If you really need relative references in Names I recommend using R1C1
reference mode rather than A1 mode, its easier to see what you are doing.

Charles


I tried every combination of absolute and relative addresses, A1 vs.
R1C1 etc and the result is the same as above

Unfortunately, I have to get this code written, so I've rethought how
I want to accomplish it

Art