#1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 172
Default Call Names

I have noticed that when I test to see if a cell has a
named range i.e. if the name was based on Sheet$A$1 and I
use sName = rng.Name.Name I would pass the name to the
variable sName

However, this doesn't always pick it up if the named
range is say Sheet$A$1:$A$1.


Why is this?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,758
Default Call Names

I'm not sure you're gonna get a good answer to your "why" question--except for
that's the way excel works.

But why would you choose to define a name so that it looks like it might be a
multiple cell range?

(There's a better chance I'll get an answer from you, than you getting an answer
from MS!)

ExcelMonkey wrote:

I have noticed that when I test to see if a cell has a
named range i.e. if the name was based on Sheet$A$1 and I
use sName = rng.Name.Name I would pass the name to the
variable sName

However, this doesn't always pick it up if the named
range is say Sheet$A$1:$A$1.

Why is this?


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,758
Default Call Names

Ahhh.

I thought you were doing extra work by putting that ":$a$1" at the end <vbg.

I've never seen anyone do this (well, until today).

If I were you, I'd get a copy of Jan Karel Pieterse's (with Charles Williams and
Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can play around with something like this:

Option Explicit
Sub testme()

Dim myName As Name
Dim TestRng As Range

For Each myName In ActiveWorkbook.Names
Set TestRng = Nothing
On Error Resume Next
Set TestRng = myName.RefersToRange
On Error GoTo 0
If TestRng Is Nothing Then
'do nothing
Else
If TestRng.Cells.Count = 1 Then
TestRng.Name = myName.Name
End If
End If
Next myName

End Sub

But don't use this if you have dynamic ranges in your names.

A name that refers to:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
Could get screwed up really badly.





ExcelMonkey wrote:

Actually I had a named range which was originally
$A$1:$A$2 which I used to feed data validation list.
Then I decided to get rid of the A2 piece. However I did
it directly within the Name dialogue box. I just simply
went in an edited $A$1:$A$2 to $A$1:$A$1 out of
laziness. When I ran my cell audit routine looking for
named ranges, I expected to see that range as it was only
attributed to 1 cell. However, the routine did not pick
it up. This caught my attention and prompted me to think
about ways in which I may want to error handle this
routine going forward assuming there might be others out
there who are as lazy as me!

Thanks

-----Original Message-----
I'm not sure you're gonna get a good answer to

your "why" question--except for
that's the way excel works.

But why would you choose to define a name so that it

looks like it might be a
multiple cell range?

(There's a better chance I'll get an answer from you,

than you getting an answer
from MS!)

ExcelMonkey wrote:

I have noticed that when I test to see if a cell has a
named range i.e. if the name was based on Sheet$A$1

and I
use sName = rng.Name.Name I would pass the name to the
variable sName

However, this doesn't always pick it up if the named
range is say Sheet$A$1:$A$1.

Why is this?


--

Dave Peterson
.


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
converting email address names in a range of cells to real names John Excel Worksheet Functions 1 May 19th 10 03:44 PM
using the Excel generic worksheet names instead of user-given names in code Paul Excel Discussion (Misc queries) 5 June 26th 09 08:44 PM
Sorting and matching rows of names with Socials with master list and eliminating the extra names Giacomo Excel Worksheet Functions 1 March 10th 07 01:52 AM
Call Center Management: How to calculate 'cost per call' Denniso6 Excel Discussion (Misc queries) 2 June 25th 06 05:01 PM
Change names of files in a folder to match names in Excel Column saybut Excel Programming 4 February 9th 04 06:26 PM


All times are GMT +1. The time now is 12:32 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"