Looping Formula to define Names

[Sayth]

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.

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

 

[Sayth]

Thank you everyone very much for the help. Resolved the error. Column names containing a number in the column name caused the error, once updated it is all working.

 

[HalfAce]

Two questions.
One, would it be acceptable to precede all these defined names with an underscore? (ie.) Replace this:
ActiveWorkbook.Names.Add Name:=NameDef.Value
with this:
ActiveWorkbook.Names.Add Name:="_" & NameDef.Value
This should eliminate the naming error for the name id5.

And Two, in your post you included the values id5, 114287, 116887 (etc.).
Are those the row 1 values that you want to name the ranges?
(I ask because your code is testing the row 1 values to be text before doing any naming.)


[EDIT:]
I also like gsbelbin's idea, except it does not account for different lengths of columns. All the defined ranges will end on the last row that column A has data...

 

[Sayth]

Two questions.
One, would it be acceptable to precede all these defined names with an underscore? (ie.) Replace this:
ActiveWorkbook.Names.Add Name:=NameDef.Value
with this:
ActiveWorkbook.Names.Add Name:="_" & NameDef.Value
This should eliminate the naming error for the name id5.

And Two, in your post you included the values id5, 114287, 116887 (etc.).
Are those the row 1 values that you want to name the ranges?
(I ask because your code is testing the row 1 values to be text before doing any naming.)

Yes preceeding witha "_" would be acceptable, I believe though that it was the number in the name that casued the error as another row 1 value that errored was "age6".

id5 is a row 1 value however the other numbers 114287, 116887 (etc.). are the contents of the column id5.

 

[p45cal]

I believe though that it was the number in the name that casued the error as another row 1 value that errored was "age6

The reason for the error wasn't because there was a number in there, but that the name could be mistaken for a cell address. ID5 is a cell, age6 is a cell (in xl2007 up). Press F5, enter age6 and click OK, and it will go there.

 

[HalfAce]

<link rel="File-List" href="file:///C:/DOCUME%7E1/PLANTO%7E1/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>

The reason for the error wasn't because there was a number in there, but that the name could be mistaken for a cell address

Hmmm, I'm not so sure.
If you try to name a range (even manually) using only numbers - or just a number at the beginning of the name - it gives the error 'That name is not valid'. (At least in xl 2000 / 2003. That may not be the case in 07 and later but I don't have that on this machine so I'm not positive.)

Seems to work fine if you precede it with an underscore though.

And, I assume this is all moot because you're up and running now. (yes?)

 

[p45cal]

<link rel="File-List" href="file:///C:/DOCUME%7E1/PLANTO%7E1/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>If you try to name a range (even manually) using only numbers - or just a number at the beginning of the name

The 'number in there' the OP was referring to was not at the start of the Name, nor was he trying to use solely numbers to name a range. In the example he gave, ID5, the error was exclusively because it was the same name as a cell address (to be exact, the same as a range address). Try erty34, it'll be happy with it, but not ert34 (xl2007 onwards).

 

[HalfAce]

The 'number in there' the OP was referring to was not at the start of the Name. In the example he gave, ID5, the error was exclusively because it was the same name as a cell address (to be exact, the same as a range address).

I agree. (Well, with id5 as the example.) I mistook the number values below that as the values in the remaining row 1 range, (what the defined names 'would be') as opposed to being values in that column.

And I agree it wont like seeing ert34 as a named range (in '07 on) for the same reason id5 won't work, but my point was (because I screwed up on what those numbers were supposed to represent) that we can't start a defined name with a numeric value. Try using 34ert and see if it'll let you do it.

Good eye catching the meaning of what was being conveyed. ('cause I sure missed it!)

 

[Sayth]

Would there be anyway to modify the name defining code to be used on importing XML. I have an issue where the order of the headers in the source xml file I import sometimes are in a different order. If I use the formula to define the names could I adapt an advanced filter to only keep certain columns and have them arranged in a cetain order.

I found this advanced filter on page 255 of the Mr Excel book.

Sub UniqueCustomerProduct()
    ' Page 255
    Dim IRange As Range
    Dim ORange As Range
    
    ' Since this is called from a button on Menu,
    ' first select the sample data sheet
    Worksheets("SalesReport").Select
    ' Clear out results of previous macros
    Range("J1:AZ1").EntireColumn.Delete
    
    ' Find the size of today's dataset
    FinalRow = Cells(65536, 1).End(xlUp).Row
    NextCol = Cells(1, 255).End(xlToLeft).Column + 2
    
    ' Set up output range. Copy heading from D1 there
    Range("D1").Copy Destination:=Cells(1, NextCol)
    Range("B1").Copy Destination:=Cells(1, NextCol + 1)
    Set ORange = Cells(1, NextCol).Resize(1, 2)
    
    ' Define the Input Range
    Set IRange = Range("A1").Resize(FinalRow, NextCol - 2)
    
    ' Do the Advanced Filter to get unique list of customers & product
    IRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ORange, Unique:=True
        
    ' Determine how many unique rows we have
    LastRow = Cells(65536, NextCol).End(xlUp).Row
    
    ' Sort the data
    Cells(1, NextCol).Resize(LastRow, 2).Sort Key1:=Cells(1, NextCol), Order1:=xlAscending, Key2:=Cells(1, NextCol + 1), Order2:=xlAscending, Header:=xlYes
    
End Sub