How to access the range's name ?

[MissyEd]

Hi

Im learnng to use excel vba and have named some of the cells in a worksheet i.e A1 = "LOCATION", A2 = "DATE" etc. I'd like to access the name of the cell from the cell reference e.g what is cell A1's name? The return should be LOCATION or similar. But when I tried Sheet1.Range(A1).Name, it returns ='Front Page'!$A$1
Any ideas?
Missy Ed
Looking to exchange ideas and tips on VB and MS Access. Drop me a line: msedbbw@hotmail.com

 

[griffinmt]

This little snippet may answer some of your question. I'll leave it up to you to 'decode' it. It was used to transform all of the named cell areas from one sheet over the the same cells on another identical sheet.

Sub MoveNames()
'
' Define all the names onto a new sheet
'
    cm = Range("CurrentMonth").Value
    Set Nams = ActiveWorkbook.Names
    For Each entry In Nams
        If InStr(1, entry.RefersToLocal, &quot;The Stats-&quot;, vbTextCompare) <> 0 Then
            ActiveWorkbook.Names.Add name:=entry.name, RefersTo:=&quot;='The Stats-&quot; & cm & &quot;'!&quot; & entry.RefersToRange.Address
        End If
    Next
End Sub

 

[MissyEd]

Thanks for your help, I've figured it out and posted the answer on a FAQ if anyone else has this query!
Missy Ed
Looking to exchange ideas and tips on VB and MS Access. Drop me a line: msedbbw@hotmail.com