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

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

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

.

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

.

.

Named range
 

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

.

.

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

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

.

.


.

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

.

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

.

.


.

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

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

.

.


.


.

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

.

.


.


.

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

.

.


.


.