Consulting

Results 1 to 16 of 16

Thread: Trying to name ranges over a number of columns

  1. #1

    Trying to name ranges over a number of columns

    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?

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    sub M_snb()
       for each it in cells(1).currentregion.columns
          it.specialcells(2).name="klum_" & it.column
       next
    End Sub
    Last edited by snb; 06-07-2016 at 07:20 AM.

  3. #3
    Nope, still get same error.

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    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

  5. #5
    Not protected, but still get same naming error.

  6. #6
    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.

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    Use a table in Excel.

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  9. #9
    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.

  10. #10
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,942
    Location
    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
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    You can't be serious
    Attached Files Attached Files

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  13. #13
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,942
    Location
    @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.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,848
    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.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #15
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,942
    Location
    @p45cal -- thanks for the reminder. I'll add that to my MakeASCII function
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •