VBA Code for Named Ranges

[fishandtril]

Hello - I am having some troubles with my VBA code I am using to automatically upload a Named Range with Comments. I receive an error when I get to this line of code

// ThisWorkbook.Names.Add Name:=nm, RefersTo:=rng //

I am not sure what is wrong with my data that is causing this error.

My data structure is:

Name	Range	Comment
MTW_2024_Vol_01_Jan_24	=MTW!$AX$300:$AX$3000	Volume 2024

The code is adding the "=MTW!$AX$300:$AX$3000" as Value and Range is the location on the Sheets("Name Upload"). The comment works.

Sub AddNamedRangesComment()

    Dim sh As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim nm As String
    Dim rng As Range
    Dim commentText As String
   
    ' Specify worksheet that contains the range name list
    Set sh = Sheets("Name Upload")
   
    ' Find last row on range name list with data (looking at column A)
    lr = sh.Cells(Rows.Count, "A").End(xlUp).Row
   
    ' Loop through all rows, starting at row 1
    For r = 1 To lr
        ' Create range
        nm = sh.Cells(r, "A").Value
        Set rng = sh.Cells(r, "B")
       
        ' Build named range
        ThisWorkbook.Names.Add Name:=nm, RefersTo:=rng
       
        ' Optionally, add a comment to the named range
        commentText = sh.Cells(r, "C").Value ' Assuming the comment is in column C
        ThisWorkbook.Names(nm).Comment = commentText
    Next r

End Sub

 

[Kaper]

With the data you shown, it seems to work. Are you sure that your data in column A does not have any spaces, etc?
HAve you checked at which row of data the code stops? is it already on first row, or somewhere below?
Don't you have (by chance ) header in a Name Upload sheet and your real data starts from row 2?


And a side, but I think important comment: I think the range to which name refers to is invalid. Assuming your B1 cell contains a string "=MTW!$AX$300:$AX$3000" (string representing, not just formula!), The code shall be:

Sub AddNamedRangesComment()

    Dim sh As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim nm As String
    Dim rng As Range
    Dim commentText As String
    Dim rngcellcontent As Variant
 
    ' Specify worksheet that contains the range name list
    Set sh = Sheets("Name Upload")
 
    ' Find last row on range name list with data (looking at column A)
    lr = sh.Cells(Rows.Count, "A").End(xlUp).Row
 
    ' Loop through all rows, starting at row 1
    For r = 1 To lr
        ' Create range
        nm = sh.Cells(r, "A").Value
        rngcellcontent = Split(sh.Cells(r, "B").Value, "!")
        Set rng = Sheets(Replace(Replace(rngcellcontent(0), "'", ""), "=", "")).Range(rngcellcontent(1))
     
        ' Build named range
        ThisWorkbook.Names.Add Name:=nm, RefersTo:=rng
     
        ' Optionally, add a comment to the named range
        commentText = sh.Cells(r, "C").Value ' Assuming the comment is in column C
        ThisWorkbook.Names(nm).Comment = commentText
    Next r

End Sub

 

[fishandtril]

Sub AddNamedRangesComment() Dim sh As Worksheet Dim lr As Long Dim r As Long Dim nm As String Dim rng As Range Dim commentText As String Dim rngcellcontent As Variant ' Specify worksheet that contains the range name list Set sh = Sheets("Name Upload") ' Find last row on range name list with data (looking at column A) lr = sh.Cells(Rows.Count, "A").End(xlUp).Row ' Loop through all rows, starting at row 1 For r = 1 To lr ' Create range nm = sh.Cells(r, "A").Value rngcellcontent = Split(sh.Cells(r, "B").Value, "!") Set rng = Sheets(Replace(Replace(rngcellcontent(0), "'", ""), "=", "")).Range(rngcellcontent(1)) ' Build named range ThisWorkbook.Names.Add Name:=nm, RefersTo:=rng ' Optionally, add a comment to the named range commentText = sh.Cells(r, "C").Value ' Assuming the comment is in column C ThisWorkbook.Names(nm).Comment = commentText Next r End Sub

Thank you very much. I was able to see what was wrong and fix the code.