VBA Assign Range Names Automatically (Workbook Scope /Worksheet Scope)

[actjfc]

Excel friends,

I would like to assign names to some specific cell references located within a range using a version of this code. It currently does not assign the names correctly. Can somebody help me by fixing it. Thanks!

Sub AssignNametoRefs()

Dim Rgn As Range
Dim RefNames As String
Dim LblSheet As String

RefNames = "B3:C5"
LblSheet = "Sheet1"

Rgn = Worksheets(LblSheet).Range(RefNames)

'Column B = Names               Example: Prices
'Column C = Cell References   Example: P5:P20
'So, the range P5:P20 is named as Prices

For i = 1 To UBound(Rgn)

   If 1 Then '1=Workbook Scope / 0 = Worksheet Scope
   
        'Single Cell Reference (***Workbook Scope***)
         ThisWorkbook.Names.Add Name:=Rgn(i, 1), RefersTo:=Rgn(i, 2)
     
   Else
    
        'Single Cell Reference (***Worksheet Scope***)
         Worksheets(LblSheet).Names.Add Name:=Rgn(i, 1), RefersTo:=Rgn(i, 2)
    
   End If
    
         Debug.Print Rgn(i, 1) & "-" & Rgn(i, 2)
   
Next i

End Sub

 

[mrmmickle1]

This syntax works for me:

Sub Test()


    'Workbook Scope
    ThisWorkbook.Names.Add Name:="HelloWorkbook", RefersTo:=ActiveSheet.Range("A2")
    'Worksheet Scope
    ThisWorkbook.Worksheets("Sheet1").Names.Add Name:="HelloSheet", RefersTo:=Range("A5")


End Sub

 

[actjfc]

My code has other issues and does not work. I have more than 300 names to handle, test, clean, delete and redefine in a model. So, I am looking for a code that helps me to do that seamlessly. I do not have the right knowledge or time to test and retest your code. Usually, an excellent, willing programmer can do it on the fly. Thanks!

 

[mrmmickle1]

	B	C
Awesome	P5:P20
Naming	J2:J10
Conventions	E3:E5
Huh	L2:L5

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #4472C4"]Range Names[/TD]
[TD="bgcolor: #4472C4"]Range [/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

Try this not sure where you are using the 1 or the 0? Maybe add this to column D

Sub AssignNametoRefs()
    
    Dim arrNames  As Variant
    Dim intLp     As Integer
    Dim sht       As Worksheet
    
    Set sht = Sheets("Sheet1") 'Set worksheet object
    arrNames = sht.Range("B2:C5").Value 'Pass range values into an array of values
    
    For intLp = 1 To UBound(arrNames)
       If 1 Then '1=Workbook Scope / 0 = Worksheet Scope
             'Workbook Scope
             ThisWorkbook.Names.Add Name:=arrNames(intLp, 1), RefersTo:=sht.Range(arrNames(intLp, 2))
       Else
            'Worksheet Scope
             sht.Names.Add Name:=arrNames(intLp, 1), RefersTo:=sht.Range(arrNames(intLp, 2))
       End If
       Debug.Print "Your Range Name:= " & arrNames(intLp, 1) & "-----> Range:= " & arrNames(intLp, 2)
    Next

End Sub

 

[actjfc]

Thanks! It works perfectly as I asked. However, you are right. What am I doing with the "1"? Here it is my modification of the code to account for the "If 1":

Assume I have this:
A ---------- B ------------------ C ------------ D
1 ----Scope ---- Range Names--- Range----Sheet
2 ----W --------- Awesome--------P5:P20
3 ----S---------- NameX-----------J2:J10----Sheet1
4 ----W----------Conventions-----E3:E5-----Sheet2
5 ----S---------- Extra-------------L2:L5
6 ----W----------Miami------------K3:K5----Sheet3

So, I modified the code. But it does not work as intended, can you spot the error? Thanks in advance!

Sub AssignNameTEST()
    
    Dim arrNames  As Variant
    Dim intLp     As Integer
    Dim sht       As Worksheet
    
    Set sht = Sheets("Sheet1") 'Set worksheet object
    arrNames = sht.Range("A2:D6").Value 'Pass range values into an array of values
    
    For intLp = 1 To UBound(arrNames)
       If arrNames(intLp, 1) = "W" Then 'W = Workbook Scope / S = Worksheet Scope
             'Workbook Scope
             ThisWorkbook.Names.Add Name:=arrNames(intLp, 2), RefersTo:=sht.Range(arrNames(intLp, 3))
       Else
            'Worksheet Scope
             sht.Names.Add Name:=arrNames(intLp, 2), RefersTo:=sht.Range(arrNames(intLp, 4) & "!" & arrNames(intLp, 3))
       End If
       Debug.Print "Scope: " & arrNames(intLp, 1) & " Your Range Name:= " & arrNames(intLp, 2) & "-----> Range:= "; arrNames(intLp, 4) & "!" & arrNames(intLp, 3)
    Next

End Sub

By the way, how do you do the nice formatted sample table in your post?

Thanks!