Results 1 to 8 of 8

Thread: vba to prompt for name of range, then prompt to select the range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vba to prompt for name of range, then prompt to select the range

    Hello
    I have a worksheet filled with attorney names on top cell and their telephones and faxes on the second cell (beneath their names),
    I wish to convert all of these into named ranges,
    so I need a macro to name each cases ( loop through all on the sheet)
    Thanks, regards

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,286
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: vba to prompt for name of range, then prompt to select the range

    Test the next macro in your sheet:

    Code:
    Sub Prompt_For_Name_Of_Range()
    '
        For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
            u = Cells(Rows.Count, j).End(xlUp).Row
            rango = Range(Cells(1, j), Cells(u, j)).Address
            nombre = Replace(Cells(1, j).Value, " ", "_")
            hoja = ActiveSheet.Name
            ActiveWorkbook.Names.Add Name:=nombre, RefersTo:="=" & hoja & "!" & rango
        Next
        MsgBox "End"
    End Sub
    Regards

  3. #3
    New Member
    Join Date
    Feb 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to prompt for name of range, then prompt to select the range

    Thanks, Im getting an error, looks like
    Compiled error
    Variable not defined
    I thinks that has to with Dim this... and Dim that...??

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,275
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: vba to prompt for name of range, then prompt to select the range

    How about
    Code:
    Sub ssurjie()
       Dim i As Long
       For i = 1 To Cells(1, Columns.Count).End(xlToLeft).column
          Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).Name = Replace(Cells(1, i).Value, " ", "_")
       Next i
    End Sub
    This will set the named range from row 2 downwards. If you want row 1 as part of the named range, change the red 2 to a 1
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,286
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: vba to prompt for name of range, then prompt to select the range

    Updated


    Code:
    Sub Prompt_For_Name_Of_Range()
    '
        dim j as long, u as long, rango as string, nombre as string, hoja as string
        For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
            u = Cells(Rows.Count, j).End(xlUp).Row
            rango = Range(Cells(1, j), Cells(u, j)).Address
            nombre = Replace(Cells(1, j).Value, " ", "_")
            hoja = ActiveSheet.Name
            ActiveWorkbook.Names.Add Name:=nombre, RefersTo:="=" & hoja & "!" & rango
        Next
        MsgBox "End"
    End Sub

  6. #6
    New Member
    Join Date
    Feb 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to prompt for name of range, then prompt to select the range

    Many thanks, it works !

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,275
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: vba to prompt for name of range, then prompt to select the range

    Not sure which of us you're talking to , but glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,286
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: vba to prompt for name of range, then prompt to select the range

    I'm also glad to help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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