VBA code to get each Column headers (in row1) in name box with its cell value as the name - possible?

[Sindrin]

Hey guys wondering if this is possible ?

Example:

Column name = product ID Name for this cell = product ID

I can then go to name box or name manager to select this in the drop down which would take me to the cell.

Hoping for this to loop to each column headers in row 1. Using its cell value as the name.

Thank you in advance !

 

[Akuini]

@Sindrin Welcome to the Forum.
Try this:

Sub a1157755a()
Dim tx As String
Dim rc As Long

rc = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To rc
    tx = Replace(Cells(1, i), " ", "_")
    ThisWorkbook.Names.Add Name:=tx, RefersTo:=Cells(1, i)
Next

End Sub

 

[Sindrin]

Thanks so much Akuini ! It worked.

If you don’t mind me asking, how can I amend the above code to run on the active cell only/the one selected whichever is faster?

Say I selected A1 then macro would run to name it’s cell value as the name?

 

[Akuini]

Try this:

Sub a1157755b()
Dim r As Range
Dim tx As String
    For Each r In Selection
        tx = Replace(r, " ", "_")
        If r <> Empty Then
        ThisWorkbook.Names.Add Name:=tx, RefersTo:=r
        End If
    Next
End Sub

 

[Sindrin]

Thank you so much worked perfectly !

 

[Akuini]

You're welcome, glad to help & thanks for the feedback

 

[Sindrin]

Hey Akuini wasn’t sure if I need to repost as new thread. But I ran into a problem whereby the cell contained symbols like “-“, “(“ and “)” and the code couldn’t run on those cells.

Is there a way you can expand the code provided to replace those other symbols with “_”?

 

[Akuini]

Try this:

Sub a1157755c()

Dim r As Range
Dim tx As String
Dim i As Long

    For Each r In Selection
            tx = r.Value
            For i = 1 To Len(tx)
                p = Mid(tx, i, 1)
                If Not p Like "[A-Za-z0-9_]" Then tx = Replace(tx, p, "_")
            Next i
        
        If r <> Empty Then ThisWorkbook.Names.Add Name:=tx, RefersTo:=r
        
    Next

End Sub

The code will replace any character that is not letter, number or underscore with underscore.

Here's an article about Excel naming rules:
excel-name-named-range-define-use

"Excel naming rules
When creating a name in Excel, there are a few rules to remember:

An Excel name should be under 255 characters long.
Excel names cannot contain spaces and most punctuation characters.
A name must begin with a letter, underscore (_), or backslash (\). If a name begins with anything else, Excel will throw an error.
Excel names are case-insensitive. For example, "Apples", "apples" and "APPLES" will be treated as the same name.
You cannot name ranges like cell references. That is, you can't give the name "A1" or "AA1" to a range.
You can use a single letter to name a range like "a", "b", "D", etc. except for the letters "r" "R", "c", and "C" (these characters are used as shortcuts for selecting a row or column for the currently selected cell when you type them in the NameBox)."

 

[Sindrin]

Thank you it worked ! Can you please also help adapt the same for your first code “a1157755a” which was based on column count rather than selection?

i tried to experiment but didn’t work (can’t paste as I’m logged in for work from home )

 

[Akuini]

Can you please also help adapt the same for your first code “a1157755a” which was based on column count rather than selection?

Try:

Sub a1157755d()
Dim r As Range
Dim tx As String, p As String
Dim i As Long

    For Each r In Range("A1").Resize(1, Cells(1, Columns.Count).End(xlToLeft).Column)
            tx = r.Value
            For i = 1 To Len(tx)
                p = Mid(tx, i, 1)
                If Not p Like "[A-Za-z0-9_]" Then tx = Replace(tx, p, "_")
            Next i
        
        If r <> Empty Then ThisWorkbook.Names.Add Name:=tx, RefersTo:=r
        
    Next
End Sub