Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to refer to programmatically Dynamice Range names
Hello,
I need help with the dynamic range names created programmatically. Follwing code generates the range names! When I tries to refer them as "workbook.xls"!"rangename" I get error message 'refence is not valid' ! I am unable to figure this! I need to use all these range names in charts, which I intend to create programmatically. Thanks Mike Sub DynamicRangeNames() Dim intRow_Num As Integer Dim strRangeName As String Dim ch As ChartObject, x Dim SearchString, SearchChar, MyPos As Integer Dim strSheetName As String, strCoverSheet As String Dim strSheetNameNew As String Dim isheet As Integer, i As Integer, j As Integer Dim iRowNum As Integer, iColNum As Integer Dim iReportRowNum As Integer, iReportColNum As Integer Dim strMonthName As String, strPrevMonthName As String Dim imonth As Integer, intNewGroup As Integer Dim strYearname As String, strReportName As String For i = 1 To ActiveWorkbook.Sheets.Count strSheetName = ActiveWorkbook.Sheets(i).Name strSheetNameNew = strSheetName intNewGroup = 4 Select Case strSheetName Case "Cover Sheet", "Template", "Old Template", "Charts" 'Do nothing, ignore this worksheets Case Else For intRow_Num = 3 To 36 If IsEmpty(Sheets(strSheetName).Cells(intRow_Num, 1)) Then intNewGroup = intRow_Num + 1 intRow_Num = intRow_Num + 1 Else intRow_Num = Sheets(strSheetName).Cells(intRow_Num, 1).Row strRangeName = Trim(Sheets(strSheetName).Cells(1, 1).Value) & "_" & _ Trim(Sheets(strSheetName).Cells(intNewGroup, 1).Value) & _ "_" & Trim(Sheets(strSheetName).Cells(intRow_Num, 1).Value) Do MyPos = InStr(1, strRangeName, " ", vbBinaryCompare) If MyPos 0 Then Mid(strRangeName, MyPos, 1) = "_" End If Loop Until MyPos <= 0 Do MyPos = InStr(1, strRangeName, "-", vbBinaryCompare) If MyPos 0 Then Mid(strRangeName, MyPos, 1) = "_" End If Loop Until MyPos <= 0 Do MyPos = InStr(1, strSheetNameNew, " ", vbBinaryCompare) If MyPos 0 Then Mid(strSheetNameNew, MyPos, 1) = "_" End If Loop Until MyPos <= 0 Do MyPos = InStr(1, strSheetNameNew, "-", vbBinaryCompare) If MyPos 0 Then Mid(strSheetNameNew, MyPos, 1) = "_" End If Loop Until MyPos <= 0 ActiveWorkbook.Names.Add Name:=strRangeName, RefersTo:= _ "=OFFSET(" & strSheetNameNew & "!" & Sheets(strSheetName).Cells(intRow_Num, 1).Address & _ ",0,1,,COUNTA(" & strSheetName & "!$" & _ intRow_Num & ":$" & intRow_Num & ")-1)" End If Next End Select Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to refer to programmatically Dynamice Range names
Why are you wanting to use the workbook name?
If you mean worksheet name, use Worksheets("worksheet_name").Range("range_name") -- HTH RP (remove nothere from the email address if mailing direct) "Mike Metal" wrote in message . com... Hello, I need help with the dynamic range names created programmatically. Follwing code generates the range names! When I tries to refer them as "workbook.xls"!"rangename" I get error message 'refence is not valid' ! I am unable to figure this! I need to use all these range names in charts, which I intend to create programmatically. Thanks Mike Sub DynamicRangeNames() Dim intRow_Num As Integer Dim strRangeName As String Dim ch As ChartObject, x Dim SearchString, SearchChar, MyPos As Integer Dim strSheetName As String, strCoverSheet As String Dim strSheetNameNew As String Dim isheet As Integer, i As Integer, j As Integer Dim iRowNum As Integer, iColNum As Integer Dim iReportRowNum As Integer, iReportColNum As Integer Dim strMonthName As String, strPrevMonthName As String Dim imonth As Integer, intNewGroup As Integer Dim strYearname As String, strReportName As String For i = 1 To ActiveWorkbook.Sheets.Count strSheetName = ActiveWorkbook.Sheets(i).Name strSheetNameNew = strSheetName intNewGroup = 4 Select Case strSheetName Case "Cover Sheet", "Template", "Old Template", "Charts" 'Do nothing, ignore this worksheets Case Else For intRow_Num = 3 To 36 If IsEmpty(Sheets(strSheetName).Cells(intRow_Num, 1)) Then intNewGroup = intRow_Num + 1 intRow_Num = intRow_Num + 1 Else intRow_Num = Sheets(strSheetName).Cells(intRow_Num, 1).Row strRangeName = Trim(Sheets(strSheetName).Cells(1, 1).Value) & "_" & _ Trim(Sheets(strSheetName).Cells(intNewGroup, 1).Value) & _ "_" & Trim(Sheets(strSheetName).Cells(intRow_Num, 1).Value) Do MyPos = InStr(1, strRangeName, " ", vbBinaryCompare) If MyPos 0 Then Mid(strRangeName, MyPos, 1) = "_" End If Loop Until MyPos <= 0 Do MyPos = InStr(1, strRangeName, "-", vbBinaryCompare) If MyPos 0 Then Mid(strRangeName, MyPos, 1) = "_" End If Loop Until MyPos <= 0 Do MyPos = InStr(1, strSheetNameNew, " ", vbBinaryCompare) If MyPos 0 Then Mid(strSheetNameNew, MyPos, 1) = "_" End If Loop Until MyPos <= 0 Do MyPos = InStr(1, strSheetNameNew, "-", vbBinaryCompare) If MyPos 0 Then Mid(strSheetNameNew, MyPos, 1) = "_" End If Loop Until MyPos <= 0 ActiveWorkbook.Names.Add Name:=strRangeName, RefersTo:= _ "=OFFSET(" & strSheetNameNew & "!" & Sheets(strSheetName).Cells(intRow_Num, 1).Address & _ ",0,1,,COUNTA(" & strSheetName & "!$" & _ intRow_Num & ":$" & intRow_Num & ")-1)" End If Next End Select Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to refer to tab names? | Excel Discussion (Misc queries) | |||
some kind of meta-way to refer to sheet and file names in Excel? | Excel Discussion (Misc queries) | |||
Can Names refer to Collections? | Excel Programming | |||
Defining Names Programmatically | Excel Programming | |||
excel calculate problem, (try to refer to defined names?) | Excel Programming |