Solved Excel VBA error I don't understand (1 Viewer)

[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