PDA

View Full Version : [SOLVED] Getting a cell's defined name



Kicker
12-10-2004, 06:49 AM
I need to get the "Defined Name" of a cell by referencing the cell. for example:


Range("C10").select
cellName = activecell.name

This gets me the reference to the cell itself rather than the name of the cell.

I have 365 named cells Jan_1, Jan_2, Dec_1, etc. I need to be able to activate the cell and do something with the "Name".

ttfn
Kicker

Richie(UK)
12-10-2004, 08:35 AM
Hi K,

Is this what you had in mind:

Sub Test()
MsgBox Sheet1.Range("A5").Name.Name
End Sub
HTH

Richie(UK)
12-10-2004, 09:08 AM
Hi K,

Thinking about it, there could be more than one named range that a cell is within. This might be overkill for what you want ... but I was curious :)


Sub Test()
Dim rngTest As Range, varNames As Variant, l As Long, strNames As String
Set rngTest = Sheet1.Range("C5")
varNames = NameofRange(rngTest)
For l = LBound(varNames) To UBound(varNames)
strNames = strNames & varNames(l) & ", "
Next l
strNames = Left(strNames, Len(strNames) - 2)
MsgBox "The specified range intersects with these named ranges:" & _
vbNewLine & strNames
End Sub

Function NameofRange(rngTest As Range) As Variant
Dim nm As Name, strArray() As String, lCnt As Long
lCnt = 0
For Each nm In rngTest.Parent.Parent.Names
If nm.RefersToRange.Parent Is rngTest.Parent Then
If Not Intersect(nm.RefersToRange, rngTest) Is Nothing Then
ReDim Preserve strArray(0 To lCnt)
strArray(lCnt) = nm.Name
lCnt = lCnt + 1
End If
End If
Next nm
NameofRange = strArray()
End Function

Only briefly tested but it seems to do the job.

Ivan F Moala
12-10-2004, 01:25 PM
Or you could...

Insert > Name > Paste > Paste list

and check the list and addresses from the resulting list.

Kicker
12-10-2004, 09:57 PM
Richie wins (tic)

this code works perfectly. As I said, I have a "named" cell for each day of the year. it doesn't matter what is in the cell, the contents are replaced by the last 2 characters of the cells name.



Private Sub justLeaveDates()
Dim c As Range
For Each c In Range("B7:H12")
If Len(c.Value) > 0 Then
c.Value = Mid(c.Name.Name, 5, 2)
End If
Next
End Sub


ttfn
Kicker

Ken Puls
12-10-2004, 10:11 PM
Hi Kicker,

As it seems you've got your issue solved, I'm going to mark the thread as such. So you know, though, you can do this yourself in future. Just check out the 'Thread Tools' menu at the top of you threads. :yes

Cheers,