Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
There are 2 named ranges, Quantity and Amount on Sheet1.
Sheet1 is then copied 3 times (the same names are also therefore copied). If sheet5 is inserted (not copied) how can I programatically detect if sheet5 has the named range Quantity without the use of absolute cell references? I'd be very grateful of advice. Geoff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
Sub testIt()
Dim x As Name On Error Resume Next Set x = ActiveSheet.Names("myName") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... There are 2 named ranges, Quantity and Amount on Sheet1. Sheet1 is then copied 3 times (the same names are also therefore copied). If sheet5 is inserted (not copied) how can I programatically detect if sheet5 has the named range Quantity without the use of absolute cell references? I'd be very grateful of advice. Geoff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
Hi,
Great - I had missed out on the On Error Resume part in my own testing. Many thanks Geoff -----Original Message----- Sub testIt() Dim x As Name On Error Resume Next Set x = ActiveSheet.Names("myName") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... There are 2 named ranges, Quantity and Amount on Sheet1. Sheet1 is then copied 3 times (the same names are also therefore copied). If sheet5 is inserted (not copied) how can I programatically detect if sheet5 has the named range Quantity without the use of absolute cell references? I'd be very grateful of advice. Geoff . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
Sorry on further testing this did not provide the correct
answers. Set up is this: Starting with 3 sheets in the workbook: Create named range "myname" on sheet1 B6 to B8 Copy before sheet2 twice This gives first 3 sheets with named range, last 2 without. My adaptation, nor the original, works correctly: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub Can you assist further please? Geoff -----Original Message----- Hi, Great - I had missed out on the On Error Resume part in my own testing. Many thanks Geoff -----Original Message----- Sub testIt() Dim x As Name On Error Resume Next Set x = ActiveSheet.Names("myName") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... There are 2 named ranges, Quantity and Amount on Sheet1. Sheet1 is then copied 3 times (the same names are also therefore copied). If sheet5 is inserted (not copied) how can I programatically detect if sheet5 has the named range Quantity without the use of absolute cell references? I'd be very grateful of advice. Geoff . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
try
Sub test() Dim ws As Worksheet, x As Name, nArray() As String Dim i As Integer, z As String, y As String For Each ws In Sheets With ws For Each x In ThisWorkbook.Names z = Replace(Replace(Replace(x.RefersTo, "'", "") _ , "=", ""), "!", "") y = Left(z, InStr(z, "$") - 1) If y = .Name Then i = i + 1: ReDim Preserve nArray(1 To i) nArray(i) = x.Name & vbTab & ": " & _ Replace(z, .Name, "") End If Next If i 0 Then MsgBox "Found " & i & " Named range(s) on " & .Name _ & vbLf & vbLf & "Name" & vbTab & ": " & "Address" & _ vbLf & Join(nArray, vbLf) Else MsgBox "No named range found on " & .Name End If End With Erase nArray: i = 0 Next End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
Hi
Still no I'm afraid as per: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Nothing Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub The returns a sheet1 = false sheet2 = true sheet3 = true sheet4 = false sheet5 = false sheet1 should read true sheet level names? yes as in Insert- Name - Define =Sheet1!$B$6:$B$8 ='Sheet1 (2)'!$B$6:$B$8 etc Geoff -----Original Message----- How does it fail to provide the correct information? While you haven't shared that information, I can guess. Add a Set x=nothing before the Set x = Sheets(i)... statement. Of course, all of this discussion assumes you are using sheet level names. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry on further testing this did not provide the correct answers. Set up is this: Starting with 3 sheets in the workbook: Create named range "myname" on sheet1 B6 to B8 Copy before sheet2 twice This gives first 3 sheets with named range, last 2 without. My adaptation, nor the original, works correctly: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub Can you assist further please? Geoff -----Original Message----- Hi, Great - I had missed out on the On Error Resume part in my own testing. Many thanks Geoff -----Original Message----- Sub testIt() Dim x As Name On Error Resume Next Set x = ActiveSheet.Names("myName") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... There are 2 named ranges, Quantity and Amount on Sheet1. Sheet1 is then copied 3 times (the same names are also therefore copied). If sheet5 is inserted (not copied) how can I programatically detect if sheet5 has the named range Quantity without the use of absolute cell references? I'd be very grateful of advice. Geoff . . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
Hi Seiya
That works perfectly every time no matter what order the sheets are in or whether there are names or not. All I have to do now is work out how it does it. :) :) Thank you Geoff -----Original Message----- try Sub test() Dim ws As Worksheet, x As Name, nArray() As String Dim i As Integer, z As String, y As String For Each ws In Sheets With ws For Each x In ThisWorkbook.Names z = Replace(Replace(Replace (x.RefersTo, "'", "") _ , "=", ""), "!", "") y = Left(z, InStr(z, "$") - 1) If y = .Name Then i = i + 1: ReDim Preserve nArray(1 To i) nArray(i) = x.Name & vbTab & ": " & _ Replace(z, .Name, "") End If Next If i 0 Then MsgBox "Found " & i & " Named range(s) on " & .Name _ & vbLf & vbLf & "Name" & vbTab & ": " & "Address" & _ vbLf & Join(nArray, vbLf) Else MsgBox "No named range found on " & .Name End If End With Erase nArray: i = 0 Next End Sub . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
In article ,
says... sheet level names? yes as in Insert- Name - Define =Sheet1!$B$6:$B$8 ='Sheet1 (2)'!$B$6:$B$8 etc No, unfortunately, that doesn't create a sheet level name. To create a sheet level name you have to use Sheet1!myName as the name. What you have is a workbook level name. When you copy the worksheet, XL creats a sheet level name within the copy. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Still no I'm afraid as per: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Nothing Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub The returns a sheet1 = false sheet2 = true sheet3 = true sheet4 = false sheet5 = false sheet1 should read true sheet level names? yes as in Insert- Name - Define =Sheet1!$B$6:$B$8 ='Sheet1 (2)'!$B$6:$B$8 etc Geoff -----Original Message----- How does it fail to provide the correct information? While you haven't shared that information, I can guess. Add a Set x=nothing before the Set x = Sheets(i)... statement. Of course, all of this discussion assumes you are using sheet level names. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry on further testing this did not provide the correct answers. Set up is this: Starting with 3 sheets in the workbook: Create named range "myname" on sheet1 B6 to B8 Copy before sheet2 twice This gives first 3 sheets with named range, last 2 without. My adaptation, nor the original, works correctly: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub Can you assist further please? Geoff -----Original Message----- Hi, Great - I had missed out on the On Error Resume part in my own testing. Many thanks Geoff -----Original Message----- Sub testIt() Dim x As Name On Error Resume Next Set x = ActiveSheet.Names("myName") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... There are 2 named ranges, Quantity and Amount on Sheet1. Sheet1 is then copied 3 times (the same names are also therefore copied). If sheet5 is inserted (not copied) how can I programatically detect if sheet5 has the named range Quantity without the use of absolute cell references? I'd be very grateful of advice. Geoff . . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
From what I can tell, the code tests if a name refers to a particular
worksheet range, not if the name itself is a worksheet level name. Also, if you decide to write a parser, you should deal with all possible cases. Just remember that a worksheet name can include all the symbols you are stripping out and/or testing for, i.e., $, ', !, and =! Here's a perfectly valid sheet name (copied directly from XL): She'$et!=2 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article .com, says... try Sub test() Dim ws As Worksheet, x As Name, nArray() As String Dim i As Integer, z As String, y As String For Each ws In Sheets With ws For Each x In ThisWorkbook.Names z = Replace(Replace(Replace(x.RefersTo, "'", "") _ , "=", ""), "!", "") y = Left(z, InStr(z, "$") - 1) If y = .Name Then i = i + 1: ReDim Preserve nArray(1 To i) nArray(i) = x.Name & vbTab & ": " & _ Replace(z, .Name, "") End If Next If i 0 Then MsgBox "Found " & i & " Named range(s) on " & .Name _ & vbLf & vbLf & "Name" & vbTab & ": " & "Address" & _ vbLf & Join(nArray, vbLf) Else MsgBox "No named range found on " & .Name End If End With Erase nArray: i = 0 Next End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
Hi
So that's why it failed on the first sheet! I've got some further reading and testing to do! Thank you very much for the pointers, your time is appreciated. Geoff -----Original Message----- In article , says... sheet level names? yes as in Insert- Name - Define =Sheet1!$B$6:$B$8 ='Sheet1 (2)'!$B$6:$B$8 etc No, unfortunately, that doesn't create a sheet level name. To create a sheet level name you have to use Sheet1!myName as the name. What you have is a workbook level name. When you copy the worksheet, XL creats a sheet level name within the copy. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Still no I'm afraid as per: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Nothing Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub The returns a sheet1 = false sheet2 = true sheet3 = true sheet4 = false sheet5 = false sheet1 should read true sheet level names? yes as in Insert- Name - Define =Sheet1!$B$6:$B$8 ='Sheet1 (2)'!$B$6:$B$8 etc Geoff -----Original Message----- How does it fail to provide the correct information? While you haven't shared that information, I can guess. Add a Set x=nothing before the Set x = Sheets(i)... statement. Of course, all of this discussion assumes you are using sheet level names. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry on further testing this did not provide the correct answers. Set up is this: Starting with 3 sheets in the workbook: Create named range "myname" on sheet1 B6 to B8 Copy before sheet2 twice This gives first 3 sheets with named range, last 2 without. My adaptation, nor the original, works correctly: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub Can you assist further please? Geoff -----Original Message----- Hi, Great - I had missed out on the On Error Resume part in my own testing. Many thanks Geoff -----Original Message----- Sub testIt() Dim x As Name On Error Resume Next Set x = ActiveSheet.Names("myName") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <002c01c54345$59d2f890 , says... There are 2 named ranges, Quantity and Amount on Sheet1. Sheet1 is then copied 3 times (the same names are also therefore copied). If sheet5 is inserted (not copied) how can I programatically detect if sheet5 has the named range Quantity without the use of absolute cell references? I'd be very grateful of advice. Geoff . . . . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
Take a look at http://www.xldynamic.com/source/xld.Names.html
-- HTH RP (remove nothere from the email address if mailing direct) "Geoff" wrote in message ... Hi So that's why it failed on the first sheet! I've got some further reading and testing to do! Thank you very much for the pointers, your time is appreciated. Geoff -----Original Message----- In article , says... sheet level names? yes as in Insert- Name - Define =Sheet1!$B$6:$B$8 ='Sheet1 (2)'!$B$6:$B$8 etc No, unfortunately, that doesn't create a sheet level name. To create a sheet level name you have to use Sheet1!myName as the name. What you have is a workbook level name. When you copy the worksheet, XL creats a sheet level name within the copy. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Still no I'm afraid as per: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Nothing Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub The returns a sheet1 = false sheet2 = true sheet3 = true sheet4 = false sheet5 = false sheet1 should read true sheet level names? yes as in Insert- Name - Define =Sheet1!$B$6:$B$8 ='Sheet1 (2)'!$B$6:$B$8 etc Geoff -----Original Message----- How does it fail to provide the correct information? While you haven't shared that information, I can guess. Add a Set x=nothing before the Set x = Sheets(i)... statement. Of course, all of this discussion assumes you are using sheet level names. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry on further testing this did not provide the correct answers. Set up is this: Starting with 3 sheets in the workbook: Create named range "myname" on sheet1 B6 to B8 Copy before sheet2 twice This gives first 3 sheets with named range, last 2 without. My adaptation, nor the original, works correctly: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub Can you assist further please? Geoff -----Original Message----- Hi, Great - I had missed out on the On Error Resume part in my own testing. Many thanks Geoff -----Original Message----- Sub testIt() Dim x As Name On Error Resume Next Set x = ActiveSheet.Names("myName") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <002c01c54345$59d2f890 , says... There are 2 named ranges, Quantity and Amount on Sheet1. Sheet1 is then copied 3 times (the same names are also therefore copied). If sheet5 is inserted (not copied) how can I programatically detect if sheet5 has the named range Quantity without the use of absolute cell references? I'd be very grateful of advice. Geoff . . . . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range
For some pointers see http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/named_formulas.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi So that's why it failed on the first sheet! I've got some further reading and testing to do! Thank you very much for the pointers, your time is appreciated. Geoff -----Original Message----- In article , says... sheet level names? yes as in Insert- Name - Define =Sheet1!$B$6:$B$8 ='Sheet1 (2)'!$B$6:$B$8 etc No, unfortunately, that doesn't create a sheet level name. To create a sheet level name you have to use Sheet1!myName as the name. What you have is a workbook level name. When you copy the worksheet, XL creats a sheet level name within the copy. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Still no I'm afraid as per: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Nothing Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub The returns a sheet1 = false sheet2 = true sheet3 = true sheet4 = false sheet5 = false sheet1 should read true sheet level names? yes as in Insert- Name - Define =Sheet1!$B$6:$B$8 ='Sheet1 (2)'!$B$6:$B$8 etc Geoff -----Original Message----- How does it fail to provide the correct information? While you haven't shared that information, I can guess. Add a Set x=nothing before the Set x = Sheets(i)... statement. Of course, all of this discussion assumes you are using sheet level names. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry on further testing this did not provide the correct answers. Set up is this: Starting with 3 sheets in the workbook: Create named range "myname" on sheet1 B6 to B8 Copy before sheet2 twice This gives first 3 sheets with named range, last 2 without. My adaptation, nor the original, works correctly: Sub test() Dim x As Name, i As Integer For i = 1 To Sheets.Count On Error Resume Next Set x = Sheets(i).Names("myname") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) Next End Sub Can you assist further please? Geoff -----Original Message----- Hi, Great - I had missed out on the On Error Resume part in my own testing. Many thanks Geoff -----Original Message----- Sub testIt() Dim x As Name On Error Resume Next Set x = ActiveSheet.Names("myName") On Error GoTo 0 MsgBox "Name myName exists: " & Not (x Is Nothing) End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <002c01c54345$59d2f890 , says... There are 2 named ranges, Quantity and Amount on Sheet1. Sheet1 is then copied 3 times (the same names are also therefore copied). If sheet5 is inserted (not copied) how can I programatically detect if sheet5 has the named range Quantity without the use of absolute cell references? I'd be very grateful of advice. Geoff . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) |