Naming cells of a column with VBA?

[joelpe]

Hi all,

I need to name all the cells of a column ("A" column) of a sheet with the cell value. I mean, if the cell says "Mister", the name would be "Mister" as well.

I know to do it manually, but I think that a macro for this will be very helpful and not too hard to make, but I don´t know how to write it.

Any suggestions?


Many thanks

 

[mole999]

a learning way is to start macro recorder, and record the actions. then try it out, the next step will be to get it to be consistent every time so come back your recorded code

 

[joelpe]

By doing that I have this code:

Sub Macro3()'
' Macro3 Macro
'


'
    ActiveWorkbook.Names.Add Name:="NAME1", RefersToR1C1:= _
        "='SHEET1!R2C2"
    ActiveWorkbook.Names("NAME1").Comment = ""
    Range("B3").Select
    ActiveWorkbook.Names.Add Name:="NAME2", _
        RefersToR1C1:="='SHEET1!R3C2"
    ActiveWorkbook.Names("NAME2").Comment = ""
    Range("B4").Select
    ActiveWorkbook.Names.Add Name:="NAME3", RefersToR1C1:= _
        "='SHEET3!R4C2"
    ActiveWorkbook.Names("NAME3").Comment = ""
End Sub

But it is a very raw code that doesnt have a loop, or a check to dont execute if the cell is blank,etc. And actually I dont get the purpose of that second line with "Comment"


By doing some research I have made this code,but It doesnt work (it would have been a surprise if it had worked):

Sub namecell()'
' nanmecell Macro
'


Dim cel As Range
Dim w As Worksheet
Set w = ActiveSheet
  For Each Cell In ActiveSheet.UsedRange.Cells
    If Cell.Value <> "" Then
        w.Names.Add Name:=Replace(ActiveCell.Text, " ", "_")
        Next cel
      
End Sub

 

[Norie]

The only problem I can see with that code is a missing End If.

Sub namecell()    '
' nanmecell Macro
'


Dim cel As Range
Dim w As Worksheet

    Set w = ActiveSheet
    
    For Each cel In ActiveSheet.UsedRange.Cells
        If cel.Value <> "" Then
            w.Names.Add Name:=Replace(ActiveCell.Text, " ", "_")
        End If
    Next cel

End Sub

 

[joelpe]

Thanks for the correction.

However,it seems like there is a problem with the line

[COLOR=#333333]w.Names.Add Name:=Replace(ActiveCell.Text, " ", "_")[/COLOR]

I copied it from another post,so I dont know exactly if the problem is the syntax or what

 

[Norie]

What is the problem?

Are you getting errors?

 

[joelpe]

What is the problem?

Are you getting errors?

Yes, when I run the Macro I get this message: "Error 1004 object-defined error or application-defined"


​And when I click "debug" it highlights that line

 

[Norie]

Do all the cells in the used range on the active sheet have values that are valid to use for naming named ranges?

 

[joelpe]

Yes, I think so. Some cells has characters like "&", but it shouldnt be a problem with the naming right?

Btw, I have realised that I need to name just cells in column A or B (depends on the sheet), and that macro I posted I think does it with all the cells of the sheet.

 

[Norie]

'&' is not a valid character for the name of a named range.

Here's a summary on what characters are allowed/not allowed:

• The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
• Names cannot be the same as a cell reference, such as Z$100 or R1C1.
• Spaces are not allowed. Underscore characters and periods may be used as word separators
• Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.