Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming working sheets | Excel Worksheet Functions | |||
Naming a Range...? | Excel Programming | |||
naming a range | Excel Programming | |||
VB Code Naming a Range (range changes each time) | Excel Programming | |||
Naming a range | Excel Programming |