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

ssurjie

New Member
Joined
Feb 21, 2014
Messages
3
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
Thanks, Im getting an error, looks like
Compiled error
Variable not defined
I thinks that has to with Dim this... and Dim that...??
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Not sure which of us you're talking to , but glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top