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

Sindrin

New Member
Joined
Jan 6, 2021
Messages
23
Office Version
  1. 2013
Platform
  1. Windows
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 !
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
@Sindrin Welcome to the Forum.
Try this:
VBA Code:
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

New Member
Joined
Jan 6, 2021
Messages
23
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
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
 
Solution

Sindrin

New Member
Joined
Jan 6, 2021
Messages
23
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thank you so much worked perfectly !
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback
 

Sindrin

New Member
Joined
Jan 6, 2021
Messages
23
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Try this:

VBA Code:
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

New Member
Joined
Jan 6, 2021
Messages
23
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Can you please also help adapt the same for your first code “a1157755a” which was based on column count rather than selection?
Try:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,998
Messages
5,628,046
Members
416,289
Latest member
Jbelisari

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top