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

actjfc

Active Member
Joined
Jun 28, 2003
Messages
416
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!

Code:
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
This syntax works for me:
Code:
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
BC
1Range NamesRange
2AwesomeP5:P20
3NamingJ2:J10
4ConventionsE3:E5
5HuhL2: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>
</tbody>
Sheet1

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

Code:
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
 
Last edited:
Upvote 0
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!

Code:
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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