Solved Excel VBA error I don't understand

[sportsguy]

Goal is to loop through a subset of sheets in the workbook and set a local sheet range name based with the same cell per sheet.

Sub CreateRangeName() On Error GoTo Err_RN Dim myCell As Range For Each ws In ActiveWorkbook.Worksheets ws.Activate Range("Z1").Select Debug.Print ActiveSheet.Name Set myCell = ActiveCell If ws.Name <> "XYZ" Then ActiveWorkbook.Worksheets(ActiveWorkSheet.Name).Names.Add Name:="Merit_Incr24", RefersToR1C1:=myCell Else End If Next ws Sheets(HomeTab).Select Range("C7").Select ActiveCell.Offset(1, 0).Activate Exit_RN: Exit Sub Err_RN: Debug.Print Err.Number & " " & Err.Description Resume Next End Sub

Error 424 Object required at RefersToR1C1:=myCell

What am I missing?
RefersToR1C1:=ActiveCell doesn't work either.
String name doesn't work either

 

[June7]

I don't think can just reference the cell object variable but need to return its address.

RefersToR1C1:=myCell.Address

Could return the ActiveCell.Address to a string variable and use that.

 

[Isaac]

June is right
Read https://docs.microsoft.com/en-us/office/vba/api/excel.name.referstor1c1

RefersToR1C1 is "a read/write string". You can't pass a range object as a required string parameter.

Though I don't know if that's the only / current problem you're experiencing, it's the low hanging fruit to correct

 

[sportsguy]

Thanks Issac, the article was helpful, and I switched to a range object due to the error message "424 Object required" as the text variable doesn't work However, this code works, and the RefersTo: is a range object, so I didn't set the range object correctly.

Here is the answer which works:

Dim cell As Range
Dim rng As Range
Dim RangeName As String
Dim CellName As String


    RangeName = "Merit_FY24"
    CellName = "Z1"
 
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate

    If ws.Name <> "DEPT" And ws.Name <> "CAPEX" And ws.Name <> "HC" And ws.Name <> "ITTOTAL" And ws.Name <> "PROJ" _
        And ws.Name <> "ACT" And ws.Name <> "Total DP&I" And ws.Name <> "Total CIOS" And ws.Name <> "Total SECURITY" And ws.Name <> "OTHER" Then
    
          Set cell = Worksheets(ws.Name).Range(CellName)
          Worksheets(ws.Name).Names.Add Name:=RangeName, RefersTo:=cell
    Else
    End If

Next ws

 

[Isaac]

well that makes sense, because RefersTo is now being set to a Range, which is correct, and name:= is now set to a string.

FYI - you can also do:

select case ws.name
case "value", "value","value"
'do nothing

case else
do something
end select