C
c1802362
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
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