Looping Formula to define Names

S

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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
Sayth said:
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
Click to expand...
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:
Upvote 0
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:
Upvote 0
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.
Click to expand...
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.
 
Upvote 0
Sayth said:
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.
Click to expand...
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?
 
Upvote 0
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>
 
Upvote 0
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/
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA Search box and button code
Replies
0
Views
124
jlcodes
J
C
  • Solved
Need Help VBA
2
Replies
10
Views
347
Trebor76
T
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
220
mlan1604
M
M
  • Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
179
igold
igold
I
  • Question
Error "Unable to set the Name property of the PivotTable class"
Replies
1
Views
193
RoryA
RoryA

Forum statistics

Threads
1,222,045
Messages
6,163,583
Members
451,846
Latest member
ajk99

Share this page

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
Back
Top