Looping Formula to define Names

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Having trouble getting a simple script to work. I want to define the contents of columns to the table header name as a defined name.

so that means that if in column A I have a range of data A2:A10 I would want that defined as a name. The name would = A1.

I know the rows may vary with different data sets loaded. I want it to loop from A1 to CA1.

Unsure how to put the Namedef into the loop.
Code:
Public Sub NameDef()

Dim NameDef As String
Dim LastRow

LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
For Each NameDef In Worksheets("Sheet1").Range(Cells(1, 1), Cells(LastRow, 74)).Cells
  If NameDef IsText = True Then
    NameDef.Name = "NameDef"
  End If
Next NameDef
End Sub
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
try:
Code:
Public Sub NameDef()
Dim LastRow
With Sheets("Sheet1")
  LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
  For Each cll In .Range("A1:CA1").Cells
    If Application.IsText(cll.Value) Then
      Range(cll.Offset(1), .Cells(LastRow, cll.Column)).Name = cll.Value
    End If
  Next cll
End With
End Sub
 

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Just trying this function. Code breaks at this line. What could cause that error.

Code:
Range(cll.Offset(1), .Cells(LastRow, cll.Column)).Name = cll.Value
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Just trying this function. Code breaks at this line. What could cause that error.

Code:
Range(cll.Offset(1), .Cells(LastRow, cll.Column)).Name = cll.Value
Chiefly, an empty cell in the top row, or a cell containing illegal characters for a name - a space say..

Choose debug when it error out and int he immediate pane (Ctrl+g if it's not visible) type
?"xxx" & cll.value & "xxx"
and press enter. It should give the contents of the cell being processed. What does it give?
 
Last edited:

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212

ADVERTISEMENT

Code:
?"xxx" & cll.value & "xxx"
xxxid5xxx
Seems to mean column E doesn't it? Could be wrong.

column E is a group identifier by number so some may belong to class 1 or class 2 etc. The column only contains numbers though so if you are in class 1 column E contains 1 next to the name.
Code:
  <table style="border-collapse: collapse; width: 62pt;" border="0" cellpadding="0" cellspacing="0" width="82"><col style="width: 62pt;" width="82">  <tbody><tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; width: 62pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border: 0.5pt solid white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23" width="82">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">2</td>  </tr> </tbody></table>
 
Last edited:

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Having trouble getting a simple script to work. I want to define the contents of columns to the table header name as a defined name.
so that means that if in column A I have a range of data A2:A10 I would want that defined as a name. The name would = A1.
I know the rows may vary with different data sets loaded. I want it to loop from A1 to CA1.
If I understand correctly, here's something you might try.
Code:
Public Sub NameDef()
Dim NameDef As Range
Dim LastRow As Long
With Worksheets("Sheet1")
  For Each NameDef In .Range(.Cells(1, 1), .Cells(1, 79))
    If WorksheetFunction.IsText(NameDef) Then
      LastRow = .Cells(Rows.Count, NameDef.Column).End(xlUp).Row
        ActiveWorkbook.Names.Add Name:=NameDef.Value, RefersToR1C1:= _
        "=Sheet1!R2C" & NameDef.Column & ":R" & LastRow & "C" & NameDef.Column
    End If
  Next NameDef
End With
End Sub
Hope it helps.
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763

ADVERTISEMENT

Code:
?"xxx" & cll.value & "xxx"
xxxid5xxx
Seems to mean column E doesn't it? Could be wrong.

column E is a group identifier by number so some may belong to class 1 or class 2 etc. The column only contains numbers though so if you are in class 1 column E contains 1 next to the name.
At first sight I don't see why this should fail (I did test my code). 2 approaches:
1. What is the error message?
2. Private Mail me at this site (MrExcel) and I'll send you an email address to send me (a version of) the file.

In the meantime, do any Names at all get defined?
 

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Yes there are 15 columns that get defined correctly. With the previous question
Code:
?"xxx" & cll.value & "xxx"
xxxid5xxx</pre>
I only just realised "id5" is the name of a column. It as the name suggests is an id feild containing a set of numbers unique as an id.

Code:
  <table style="border-collapse: collapse; width: 41pt;" border="0" cellpadding="0" cellspacing="0" width="55"><col style="width: 41pt;" width="55">  <tbody><tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; width: 41pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 1.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(114, 124, 163);" height="23" width="55">id5</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">114287</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">116887</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">117654</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">113187</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">119136</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">131760</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">109153</td>  </tr> </tbody></table>
 

gsbelbin

Active Member
Joined
Aug 29, 2008
Messages
336
A simpler option would be to use this

Code:
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.CreateNames Top:=True
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,227
Messages
5,509,920
Members
408,763
Latest member
kinhcuonglucvinh

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top