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

ssurjie

New Member
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
 

DanteAmor

Well-known Member
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
 

ssurjie

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

Fluff

MrExcel MVP, Moderator
How about
Code:
Sub ssurjie()
   Dim i As Long
   For i = 1 To Cells(1, Columns.Count).End(xlToLeft).column
      Range(Cells([COLOR=#ff0000]2[/COLOR], 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
 

DanteAmor

Well-known Member
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
 

Fluff

MrExcel MVP, Moderator
Not sure which of us you're talking to , but glad we could help & thanks for the feedback
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top