Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning a named range source reference
Using XL03
I am using the code below, trying to create a list of named ranges and their source references. I'm having two problems. (1) There are some other names (named objects) in the workbook, and I don't want to return those; I just want to return named ranges, and (2) The code below returns the resulting range referred to, but not the source reference. For example, if the named range is "=offset(A1,1,1)" then I need to see that offset statement, not the resulting range of "B2". I tried everything that made sense from the help object model under names, and I've tried various guesses beyond that (.source, etc) but haven't lucked across the right keyword. Any help would be greatly appreciated! Keith Sub MakeNRList() Dim n As Name On Error Resume Next For Each n In ThisWorkbook.Names i = i + 1 Sheet3.Range("A" & i) = n.Name Sheet3.Range("B" & i) = n.RefersToRange.Address(False, False) '<-- Next n End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning a named range source reference
Try this
On Error Resume Next Sheet3.Range("B" & i) = n.RefersToRange.Address(False, False) If Err < 0 Then Application.GoTo Name.Name Sheet3.Range("B"&I) =Selection.Address End If On Error GoTo 0 -- HTH, Barb Reinhardt "ker_01" wrote: Using XL03 I am using the code below, trying to create a list of named ranges and their source references. I'm having two problems. (1) There are some other names (named objects) in the workbook, and I don't want to return those; I just want to return named ranges, and (2) The code below returns the resulting range referred to, but not the source reference. For example, if the named range is "=offset(A1,1,1)" then I need to see that offset statement, not the resulting range of "B2". I tried everything that made sense from the help object model under names, and I've tried various guesses beyond that (.source, etc) but haven't lucked across the right keyword. Any help would be greatly appreciated! Keith Sub MakeNRList() Dim n As Name On Error Resume Next For Each n In ThisWorkbook.Names i = i + 1 Sheet3.Range("A" & i) = n.Name Sheet3.Range("B" & i) = n.RefersToRange.Address(False, False) '<-- Next n End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning a named range source reference
Barb-
Thank you for the quick response. The n.RefersToRange.Address(False, False) is giving me the resulting range (e.g., "B1" from the original post) and not the original formula as seen in the name manager. I've tinkered with the inner loop as the compiler didn't like the "Name.Name" but I think it was intended to just go to that range. Updated code pasted below. With the updated code, all of my named ranges have a name and referstoaddress; about half of the remaining names have a referstoaddress and the other half have a selection.address. I was trying to exclude the names that weren't ranges just for convenience (I counted, and I have over 300 named ranges, and hundreds of other names as well). The bigger problem is still finding a way to extract the original named range formula, as I have to validate that all 300 are accurate before giving this workbook to my internal customer, as errors will be very difficult to detect just from looking at the output data. Thanks! Keith Revised code: ------------------------- Sub MakeNRList() Dim n As Name On Error Resume Next For Each n In ThisWorkbook.Names i = i + 1 Sheet3.Range("G" & i) = n.Name Sheet3.Range("H" & i) = n.RefersToRange.Address(False, False) If Err < 0 Then Application.Goto Name '.Address '.Name Sheet3.Range("J" & i) = Selection.Address End If Next n On Error GoTo 0 End Sub Many thanks, Keith "Barb Reinhardt" wrote: Try this On Error Resume Next Sheet3.Range("B" & i) = n.RefersToRange.Address(False, False) If Err < 0 Then Application.GoTo Name.Name Sheet3.Range("B"&I) =Selection.Address End If On Error GoTo 0 -- HTH, Barb Reinhardt "ker_01" wrote: Using XL03 I am using the code below, trying to create a list of named ranges and their source references. I'm having two problems. (1) There are some other names (named objects) in the workbook, and I don't want to return those; I just want to return named ranges, and (2) The code below returns the resulting range referred to, but not the source reference. For example, if the named range is "=offset(A1,1,1)" then I need to see that offset statement, not the resulting range of "B2". I tried everything that made sense from the help object model under names, and I've tried various guesses beyond that (.source, etc) but haven't lucked across the right keyword. Any help would be greatly appreciated! Keith Sub MakeNRList() Dim n As Name On Error Resume Next For Each n In ThisWorkbook.Names i = i + 1 Sheet3.Range("A" & i) = n.Name Sheet3.Range("B" & i) = n.RefersToRange.Address(False, False) '<-- Next n End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning a named range source reference
Ugh-
Turns out, what I need is .RefersTo I had tried that before but only seen values (such as when using .value), so I kept testing. Turns out that the original formula is there, but when my code pasted it into the cell it evaluates, and then I was seeing the number and not the original formula. I added a single quote, and now everything is working as expected. Thanks again, Keith "ker_01" wrote: Using XL03 I am using the code below, trying to create a list of named ranges and their source references. I'm having two problems. (1) There are some other names (named objects) in the workbook, and I don't want to return those; I just want to return named ranges, and (2) The code below returns the resulting range referred to, but not the source reference. For example, if the named range is "=offset(A1,1,1)" then I need to see that offset statement, not the resulting range of "B2". I tried everything that made sense from the help object model under names, and I've tried various guesses beyond that (.source, etc) but haven't lucked across the right keyword. Any help would be greatly appreciated! Keith Sub MakeNRList() Dim n As Name On Error Resume Next For Each n In ThisWorkbook.Names i = i + 1 Sheet3.Range("A" & i) = n.Name Sheet3.Range("B" & i) = n.RefersToRange.Address(False, False) '<-- Next n End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a named range as a data source for a chart | Excel Worksheet Functions | |||
Named Range as Chart Source | Excel Programming | |||
Looking up a named range and returning value into a cell | Excel Discussion (Misc queries) | |||
OFFSET function in named range returning wrong # of rows | Excel Worksheet Functions | |||
Combobox and named range as source | Excel Programming |