Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Name object bug
All,
I want to add a sheet name referencing a multi area range on another sheet. The normal code to do this actually fails: Sub t() Dim rngMyRange As Range Dim shtMysheet As Worksheet Dim shtMyOthersheet As Worksheet Set shtMysheet = ActiveWorkbook.Worksheets(1) Set shtMyOthersheet = ActiveWorkbook.Worksheets(2) Set rngMyRange = shtMyOthersheet.Range("A1,A3") shtMysheet.Names.Add "MyName", "=" & rngMyRange.Address(, , , True) Debug.Print shtMysheet.Names("MyName").RefersTo shtMysheet.Names("MyName").Delete End Sub Only the first area of the range is on the correct sheet. all other areas are on the sheet of the defined name. Anybode got some code to get around this? DM Unseen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Name object bug
Duh,
following could seems to work around this issue: Dim rngArea as Range DIm strAddress as string If rngMyRange .Areas.Count 1 Then strAddress = vbNullString For Each rngArea In rngNames.Areas If Len(strAddress) 0 Then strAddress = strAddress & Application.International(xlListSeparator) strAddress = strAddress & rngArea.Address(, , , True) Next rngArea Else strAddress = rngNames.Address(, , , True) End If shtMysheet.Names.Add "MyName", "=" & strAddress Dm Unseen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Name object bug
What does:
rngMyRange.Name = "MyName" do? -- Regards, Tom Ogilvy "DM Unseen" wrote in message ups.com... Duh, following could seems to work around this issue: Dim rngArea as Range DIm strAddress as string If rngMyRange .Areas.Count 1 Then strAddress = vbNullString For Each rngArea In rngNames.Areas If Len(strAddress) 0 Then strAddress = strAddress & Application.International(xlListSeparator) strAddress = strAddress & rngArea.Address(, , , True) Next rngArea Else strAddress = rngNames.Address(, , , True) End If shtMysheet.Names.Add "MyName", "=" & strAddress Dm Unseen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Name object bug
Thanks Tom,
That also works OK also, *but* it will only work within 1 workbook, i.e. the created Named Range does not qualify the workbook name. My solution will work *across* workbooks as well. But right now I do not want/need that, so I'll use your suggestion instead, which is a lot shorter& easier as well;) Dm Unseen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Label Options - Forms Object vs Control Box Object | Excel Discussion (Misc queries) | |||
Option button object proeprties or object not found in vba | Excel Programming | |||
Confusion about how the Window object fits into the Excel object model | Excel Programming | |||
returning pivottable object from a range object | Excel Programming | |||
Range object to Array object conversion | Excel Programming |