Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions | |||
Call Center Management: How to calculate 'cost per call' | Excel Discussion (Misc queries) | |||
Change names of files in a folder to match names in Excel Column | Excel Programming |