?
Solved

Return defined name of cell

Posted on 2010-11-24
5
Medium Priority
?
398 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

569 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