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

[ssurjie]

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]

Test the next macro in your sheet:

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]

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

 

[Fluff]

How about

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]

Updated

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

 

[ssurjie]

Many thanks, it works !

 

[Fluff]

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

 

[DanteAmor]

I'm also glad to help