PDA

View Full Version : [SOLVED] Trying to name ranges over a number of columns



Obfuscated
06-07-2016, 06:40 AM
I am trying to assign named ranges from column A to column Y. Each column has a header, and each column varies in # of rows of info. I am trying to dynamically name each column for future reference. My code is:

Dim rngselect As Range
Dim ds As String
Set rngselect = ws.Range("Sections") ' (A1:Y1)= column headers
For Each cell In rngselect
With ws
Set lastcell = .Cells(.Rows.Count, rngselect.Column).End(xlUp)
rn = lastcell.Row
End With
ds = Chr$(rngselect.Column + 64) & "2:" & Chr$(rngselect.Column + 64) & rn

ws.Names.Add Name:=cell, RefersTo:=ds, Visible:=True
Next cell


The returns for the first column (A) are:
' cell for "A1" = "HP12"
' ds = "A2:A6"

I keep getting the error msg that says my name is not valid. Can someone tell me my mistake in the code above please?

snb
06-07-2016, 07:00 AM
sub M_snb()
for each it in cells(1).currentregion.columns
it.specialcells(2).name="klum_" & it.column
next
End Sub

Obfuscated
06-07-2016, 07:06 AM
Nope, still get same error.

snb
06-07-2016, 07:19 AM
Not this one (hope your sheet isn't protected ?)


Sub M_snb()
For Each it In cells(1).currentregion.columns
it.specialcells(2).name="klum_" & it.column
Next
End Sub

Obfuscated
06-07-2016, 07:29 AM
Not protected, but still get same naming error.

Obfuscated
06-07-2016, 07:36 AM
Got it to work, but I need the first entry in column A to be the range name
Cell "A1" = "HP12"
Cell "B1" = "HP14"
etc...
Those headers are what I want as the range names, not the column number.

snb
06-07-2016, 07:41 AM
Use a table in Excel.

SamT
06-07-2016, 07:43 AM
Dim ds As String
Dim nm as String

For Each cel In ws.Range("Sections")
nm = Replace(Cel, " ", "_") 'Spaces not allowed in Names
If ISNumeric(Left(nm, 1) then nm = "_" & nm 'Numeric first characters not allowed

ds = Range(Cel.Offset(1), Cells(Rows.Count, Cel.Column).End(xlUP)).Address

ws.Names.Add Name:=nm, RefersTo:=ds, Visible:=True
Next cell

Obfuscated
06-07-2016, 08:09 AM
For whatever reason, I get the same error as before for naming convention.
All the variables come back with correct info, but it won't name the ranges.
I put your code in a separate sub and called it... same error.
Tried adding "_" before name, skipped through whole process without naming any area.

Paul_Hossler
06-07-2016, 09:56 AM
I usually do something like this with a formula to make named ranges dynamic.

There are rules on valid names



Option Explicit
Sub test()
Dim iCol As Long, i As Long

For iCol = 1 To ActiveSheet.Cells(1, 1).End(xlToRight).Column
Call NameAddDynamic(ActiveSheet.Cells(1, iCol))
Next iCol
With ActiveWorkbook
For i = 1 To .Names.Count
MsgBox .Names(i).Name & " --- " & .Names(i).RefersTo & " --- " & .Names(i).RefersToRange.Address
Next i
End With
End Sub

Sub NameAddDynamic(R As Range)
Dim sFormula As String, sName As String
Dim rName As Range

Set rName = R.Cells(1, 1)

sName = MakeASCII(rName.Value)

With R.Parent
sFormula = "=OFFSET("
sFormula = sFormula & "'" & .Name & "'!" & rName.Address(True, True) & ",0,0,"
sFormula = sFormula & "COUNTA("
sFormula = sFormula & "'" & .Name & "'!" & rName.EntireColumn.Address(True, True) & "),1)"
.Parent.Names.Add Name:=sName, RefersTo:=sFormula
End With
End Sub
Private Function MakeASCII(S As String) As String
Dim i As Long
Dim s1 As String, c As String

c = Left(S, 1)

Select Case c
Case "0" To "9"
S = "_" & S
Case Else
S = S
End Select

For i = 1 To Len(S)

c = Mid(S, i, 1)

Select Case c
Case "a" To "z", "A" To "Z", "0" To "9", "_"
s1 = s1 & c
Case " "
s1 = s1 & "_"
End Select
Next i
MakeASCII = s1
End Function

snb
06-07-2016, 12:04 PM
You can't be serious

SamT
06-07-2016, 12:04 PM
Are there any Names already in the Sheet?

Dim nm as Long

With ws
For nm = .Names.Count to 1 step -1
.Names(nm).delete
Next

Paul_Hossler
06-07-2016, 12:31 PM
@snb --

Actually I was

1. Yours fails if the 'Name to Be' in row 1 will not be a valid name

2. Yours 'hard codes' the RefersTo range so that is data is added or deleted, the named range doesn't reflect the changes


I just prefer my approach.

p45cal
06-07-2016, 02:20 PM
Amongst many other reasons for your code to fail is this one:

' cell for "A1" = "HP12"HP12 is an invalid name because it the same as a cell address; if you were to use it in a formula how would Excel be able to differentiate your Name from the cell HP12 - it would appear exactly the same.
One way is to prefix the header with, say, an underline '_'.
The following code will do the same as your code intends (as long as there is no other reason for the header being an invalid name):
Set ws = ActiveSheet 'had to add this line as there was no evidence as to what ws was.
With ws
For Each cell In .Range("Sections")
.Range(cell.Offset(1), .Cells(.Rows.Count, cell.Column).End(xlUp)).Name = "_" & cell
Next cell
End With
Just be aware (both yours and my code), if the column is empty except for the header, the named range will be the header and the cell below it.

Paul_Hossler
06-07-2016, 05:34 PM
@p45cal -- thanks for the reminder. I'll add that to my MakeASCII function

Obfuscated
06-08-2016, 03:06 AM
First of all, let me say thanks to everyone for the answers and ideas. P45Cal, you are correct, I was trying to name the range "HP12" and that was a cause of the failed name try. What I finally ended up with is this:


For i = 65 To 89
ds = Chr$(i) & "1"
Range(ds).Select
cel = Range(ds).Value
If Left(Range(ds), 1) = "H" Then
cel = "_" & Range(ds).Value
End If
Range(Selection.Offset(1, 0), Selection.End(xlDown)).Name = cel
Next I

This seems to do the job nicely. Thanks again guys, keep exceling.