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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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,454
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>
 

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
HalfAce tested your code and it has the same error. think I understand it though. The is an illegal character or space that it can't convert or use as a name, I think.

4862707537

http://www.flickr.com/photos/46087571@N04/4862707537/
 

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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,169
Members
418,430
Latest member
Chlwls808

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