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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 09:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com