"Method 'Range' of 'object' Global Failed" error

[Ark68]

I am receiving a "Method 'Range' of 'object' Global Failed" error with the line of code highlighted in red below ...

        Missing_rental ar, pr, mr
        
        With uf1_assess_sched
            .uf1_tb7.Value = ar
            .uf1_tb8.Value = pr
            .uf1_tb7.Locked = True
            .uf1_tb8.Locked = True
            .uf1_tb7.ForeColor = RGB(0, 52, 89)
            .uf1_tb8.ForeColor = RGB(0, 52, 89)
            If ar = 0 Then
                .uf1_active.Value = False
                .uf1_active.Locked = True
            Else
                .uf1_active.Value = True
                .uf1_active.Locked = False
            End If
            If pr = 0 Then
                .uf1_passive.Value = False
                .uf1_passive.Locked = True
            Else
                .uf1_passive.Value = True
                .uf1_passive.Locked = False
            End If
            With .uf1_listbox3
                .Clear
                .ForeColor = RGB(0, 52, 89)
                .ColumnCount = 4
                .ColumnWidths = "30;100;80;95"
                .List = Range("missing_all").Value
                .ListStyle = fmListStyleOption
                '.Locked = True
                .MultiSelect = fmMultiSelectSingle
            End With
        End With

This code calls procedure 'missing_rental' which is used to calculate values needed to be displayed in userform 'uf1_assess_sched'
It is in procedure 'missing_rental' in which the named range "missing_all" is set. The named range "missing_all" refers to the dynamic set of data M1:Px in workbook schedule.csv. worksheets temp_ws.

Data exists in this range M1:P17. The named range should refer to this.

Here is the code for procedure "missing_rental"

Sub Missing_rental(ByRef ar As Long, ByRef pr As Long, ByRef mr As Long)

    Dim c1 As Long, c2 As Long
    Dim l_rentalno As Long
    Dim s_main As String, s_sub As String
    Dim temp_ws As Worksheet
    Dim l_temp_lrow As Long
    Dim rw_pr_start As Long, rw_ar_start As Long, rw_pr_end As Long, rw_ar_end As Long
    
    Set temp_ws = wb_sched.Worksheets("temp_ws")
    
    Application.ScreenUpdating = False
    
    mr = 0
    pr = 0
    ar = 0
    
    With ws_sched
        If .AutoFilterMode Then .AutoFilterMode = False
        For c1 = 2 To llastrow
            If .Cells(c1, 1) = "" Then  'only check those rentals not eliminated through redundancy check
                l_rentalno = .Cells(c1, 3)
                l_cntrn = WorksheetFunction.CountIf(ws_rd.Range("A:A"), l_rentalno)
                If l_cntrn = 0 Then 'rental doesn't exist in database
                    'mr = mr + 1
                    For c2 = 2 To 8
                        s_sub = ws_lists.Cells(c2, 59)
                        s_main = .Cells(c1, 9)
                        If InStr(s_main, s_sub) <> 0 Then 'passive
                            .Cells(c1, 1) = "pass"
                            'pr = pr + 1
                        End If
                    Next c2
                    If .Cells(c1, 1) = "" Then
                        .Cells(c1, 1) = "act"
                        'ar = ar + 1
                    End If
                End If
            End If
        Next c1
        
        pr = WorksheetFunction.CountIf(.Range("A:A"), "pass")
        ar = WorksheetFunction.CountIf(.Range("A:A"), "act")
        mr = pr + ar
        If mr > 0 Then  'prepare missing rentals lists
            .AutoFilterMode = False
            With .Range("A1:W1")
                .AutoFilter
                .AutoFilter Field:=1, Criteria1:="pass", Operator:=xlOr, Criteria2:="act"
            End With
            .Range("A2:A" & llastrow).SpecialCells(xlCellTypeVisible).Copy
            temp_ws.Range("L1").PasteSpecial xlPasteValuesAndNumberFormats
            .Range("C2:C" & llastrow).SpecialCells(xlCellTypeVisible).Copy
            temp_ws.Range("M1").PasteSpecial xlPasteValuesAndNumberFormats
            .Range("H2:H" & llastrow).SpecialCells(xlCellTypeVisible).Copy
            temp_ws.Range("P1").PasteSpecial xlPasteValuesAndNumberFormats
            .Range("I2:I" & llastrow).SpecialCells(xlCellTypeVisible).Copy
            temp_ws.Range("O1").PasteSpecial xlPasteValuesAndNumberFormats
            .Range("P2:P" & llastrow).SpecialCells(xlCellTypeVisible).Copy
            temp_ws.Range("N1").PasteSpecial xlPasteValuesAndNumberFormats
            Application.CutCopyMode = False
            'End With
            With temp_ws
                l_temp_lrow = .Cells(.Rows.Count, "M").End(xlUp).row
                .Range("L1:P" & l_temp_lrow).Sort key1:=.Range("M1"), order1:=xlAscending, Header:=xlNo
              'eliminate duplicates
                For c2 = l_temp_lrow To 2 Step -1
                    If .Cells(c2, 13).Value = .Cells(c2 - 1, 13).Value Then
                    .Range(.Cells(c2, 12), .Cells(c2, 16)).Delete shift:=xlUp
                    End If
                Next c2
              'sort according to rental type
                .Range("L1:P" & l_temp_lrow).Sort key1:=.Range("L1"), order1:=xlAscending, key2:=.Range("M1"), order1:=xlAscending, Header:=xlNo
                l_temp_lrow = .Cells(.Rows.Count, "M").End(xlUp).row
                
                pr = WorksheetFunction.CountIf(.Range("L:L"), "pass")
                ar = WorksheetFunction.CountIf(.Range("L:L"), "act")
                mr = pr + ar
                Workbooks("Sports15c.xlsm").Names.Add Name:="missing_all", RefersTo:=temp_ws.Range("M1:P" & l_temp_lrow)
                If ar > 0 Then
                    rw_ar_start = WorksheetFunction.Match("act", .Range("L1:L" & l_temp_lrow), 0)
                    rw_ar_end = rw_ar_start - 1 + ar
                    Workbooks("Sports15c.xlsm").Names.Add Name:="missing_act", RefersTo:=temp_ws.Range(.Cells(rw_ar_start, 13), .Cells(rw_ar_end, 16))
                End If
                If pr > 0 Then
                    rw_pr_start = WorksheetFunction.Match("pass", .Range("L1:L" & l_temp_lrow), 0)
                    rw_pr_end = rw_pr_start - 1 + pr
                    Workbooks("Sports15c.xlsm").Names.Add Name:="missing_pass", RefersTo:=temp_ws.Range(.Cells(rw_pr_start, 13), .Cells(rw_pr_end, 16))
                End If
            End With
        Else
            pr = 0
            ar = 0
            mr = pr + ar
        End If
    End With
    With ws_vh
        .Range("B3") = ar
        .Range("B4") = pr
        .Range("B2") = mr
    End With
    'MsgBox "Total missing: " & mr & Chr(13) & Chr(13) & "Total passive: " & pr & Chr(13) & "Total active: " & ar
    Application.ScreenUpdating = True
End Sub

Any help in resolving this error will be greatly appreciated! If additional informationis needed to help diagnose, please ask. I'm unsure of just how much is required.

 

[JoeMo]

Haven't looked at your code so this may be off base. If the named range "missing_all" is not scoped at workbook level in the workbook the code that returns the error is being run from, or it has workbook-level scope, but not in the workbook the code is being run from you may need to qualify the sheet and/or workbook the named range is attached to.

 

[Ark68]

Hello Joe, thank you so much for sharing your thoughts...

I'm not sure what you mean by scoped, so can't say for certain I'm offering anything in return to accept or dismiss that premise.

All code is in workbook "sports15c.xlsm".
I have successfully populated uf1_assess_sched listbox3 using named range "missing_all" previously in the userform's initialization routine.
What this code is trying to do is update the contents of listbox by basically re-running the procudure for creating the list, which is the named range "missing_all".

I do have a second workbook open, schedule.csv. Perhaps the name range is being assigned to that workbook? Not certain how to check? But the code for creating the named range specifically references workbook "sports15c.xlsm" to add the named range.

 

[JoeMo]

Hello Joe, thank you so much for sharing your thoughts...

I'm not sure what you mean by scoped, so can't say for certain I'm offering anything in return to accept or dismiss that premise.

All code is in workbook "sports15c.xlsm".
I have successfully populated uf1_assess_sched listbox3 using named range "missing_all" previously in the userform's initialization routine.
What this code is trying to do is update the contents of listbox by basically re-running the procudure for creating the list, which is the named range "missing_all".

I do have a second workbook open, schedule.csv. Perhaps the name range is being assigned to that workbook? Not certain how to check? But the code for creating the named range specifically references workbook "sports15c.xlsm" to add the named range.

If you go to Formulas>Defined Names>NameManager you can see the scope of your defined names. From your reply, I don't think scope is the issue.

 

[Ark68]

It appears I had to make the workbook holding the name as the activeworkbook.