Set command error...

[dplum]

I'm getting an error on the statement "Set vRefersto_Cell_Range = vSheet_Range".

I believe that "vRefersto_Cell_Range" is needed for the "Names.Add" statement

Any help is much appreciated. BTW, I realize that a Name can be easily assigned using the Name box, but I've created this macro to simply test the use of Names.Add statement.

Sub NameSelectedCells()
    Dim vInputCells, vRefersto_Cell_Range As Range
    Set vInputCells = Application.InputBox(prompt:="Select cells to be named...", Left:=1, Top:=1, Type:=8)
    'type:=8 for cell references or ranges; change the Left and Top arguments to position the InputBox
    vSheet_Range = "'" & ActiveSheet.Name & "'!" & vInputCells.Address(ReferenceStyle:=xlA1)
    Set vRefersto_Cell_Range = vSheet_Range
    Do
        vRangeName = InputBox("Assign the Range[" & vSheet_Range & "]" & vbCr & _
                              "to the following Name...")
        If vRangeName = "" Then Exit Sub
        On Error Resume Next
        Names.Add Name:=vRangeName, RefersTo:=vRefersto_Cell_Range, Visible:=True
        If Err.Number = 0 Then Exit Sub
        MsgBox "RangeName... [" & vRangeName & "] is Invalid!" & vbCr & _
            "Must Not start with a Number, Must Not contain a Space, Only underscore (_) special character is allowed"
    Loop
End Sub

 

[Jaafar Tribak]

I'm getting an error on the statement "Set vRefersto_Cell_Range = vSheet_Range".

I believe that "vRefersto_Cell_Range" is needed for the "Names.Add" statement

Any help is much appreciated. BTW, I realize that a Name can be easily assigned using the Name box, but I've created this macro to simply test the use of Names.Add statement.

Sub NameSelectedCells()
    Dim vInputCells, vRefersto_Cell_Range As Range
    Set vInputCells = Application.InputBox(prompt:="Select cells to be named...", Left:=1, Top:=1, Type:=8)
    'type:=8 for cell references or ranges; change the Left and Top arguments to position the InputBox
    vSheet_Range = "'" & ActiveSheet.Name & "'!" & vInputCells.Address(ReferenceStyle:=xlA1)
    Set vRefersto_Cell_Range = vSheet_Range
    Do
        vRangeName = InputBox("Assign the Range[" & vSheet_Range & "]" & vbCr & _
                              "to the following Name...")
        If vRangeName = "" Then Exit Sub
        On Error Resume Next
        Names.Add Name:=vRangeName, RefersTo:=vRefersto_Cell_Range, Visible:=True
        If Err.Number = 0 Then Exit Sub
        MsgBox "RangeName... [" & vRangeName & "] is Invalid!" & vbCr & _
            "Must Not start with a Number, Must Not contain a Space, Only underscore (_) special character is allowed"
    Loop
End Sub

Try :

 Set vRefersto_Cell_Range = Range(vSheet_Range)

 

[njimack]

You've Dim'd vRefersto_Cell_Range as a Range, but you're trying to assign a string to it.