[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Return defined name of cell

Posted on 2010-11-24
5
Medium Priority
?
396 Views
Last Modified: 2012-05-10
I'm creating a verification code for a workbook and want to autopopulate cells based on the colour of the cell.

Some cells need to be filled in with that cells defined name so that they can be easily tracked throughout the workbook. I've attached the code that I am trying to do this with (stuck getting the second loop to return cells defined name) but it is currently just returing the cell's location on the sheet not the defined name of that cell.
Private Sub CommandButton5_Click()

    Dim rCell As Range
    Dim pg As Worksheet
    Dim N As Single
    Dim aColor As Long ' Data entry for random number (Light Blue)
    Dim bColor As Long ' Data entry for words - set by cell defined name (Very Pale Blue)
    Dim cColor As Long ' Verification data that shouldn't be changed but should be removed prior to issue (Tan)
    Dim dColor As Long ' Data/Formulas that shouldn't be changed or removed prior to issue (Very Pale Green)
    Randomize ' Initiate random number generator
    aColor = RGB(153, 204, 255) 'Specify the color range for random numbers
    bColor = RGB(204, 255, 255)
    cColor = RGB(255, 204, 153)
    dColor = RGB(204, 255, 204)


For N = 1 To Sheets.Count
    For Each rCell In Sheets(N).Range("A1:AZ500")
        If rCell.Interior.Color = aColor Then
        rCell.Value = 0.6 + (0.3 * Rnd)
        Else
        End If
    Next rCell
Next N

For N = 1 To Sheets.Count
    For Each rCell In Sheets(N).Range("A1:AZ500")
        If rCell.Interior.Color = bColor Then
        rCell.Value = rCell.Name
        Else
        End If
    Next rCell
Next N

End Sub

Open in new window

0
Comment
Question by:Haydan
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 34209582
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 34209610
0
 
LVL 81

Accepted Solution

by:
byundt earned 1000 total points
ID: 34209648
If the workbook has been saved and the name refers to a single cell, then you can use:
rCell.Value = rCell.Name.Name
0
 

Author Closing Comment

by:Haydan
ID: 34209721
Why do you have to go .Name.Name?
0
 
LVL 81

Expert Comment

by:byundt
ID: 34209847
Haydan,
The explanation is a little tricky:
The Name property of a Range object returns a Name object
The default property of a Name object is its address
The Name property of a Name object is the name of that object

So following the first and second points, rCell.Name will return something like =Sheet1!A1
Following all three points, rCell.Name.Name returns what you need--the name of the cell

I had to look up the syntax of the Name property and object to arrive at the explanation. But I've encountered this question before, and it was far easier for me to remember the stuttering pattern of .Name.Name as the way to get the answer.

Brad
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question