Unable to get the Vlookup property of the WorksheetFunction class

[Ark68]

Is anyone able to suggest a resolution to to the "Unable to get the Vlookup property of the WorksheetFunction class" I am getting highlighted line in the code below?

Sub services_staff1(ByVal ws_core As Object, ByVal ws_corestaff As Object, ByVal ws_th As Worksheet, t_min As Variant, d1 As String, crew_open As String, ofb As Double)
    Dim rw As Long, lk As Long
    Dim staff_range As Range
    Dim l_diffa As Long
    
    
    Set staff_range = ws_corestaff.Range("A4:E18")
    
    With ws_th
        .Activate
        rw = 2
        If t_min >= ws_corestaff.Range("D4") And t_min <= ws_corestaff.Range("E4") Then
            .Cells(rw, ofb) = "CUE1"
            .Cells(rw, ofb + 1) = Format(t_min - ws_corestaff.Range("D4"), "h")
            '.Range("A" & rw) = "CUE1"
            '.Range("B" & rw) = Format(t_min - ws_corestaff.Range("D4"), "h")
            rw = rw + 1
        End If
        If t_min >= ws_corestaff.Range("D6") And t_min <= ws_corestaff.Range("E6") Then
            .Cells(rw, ofb) = "CUL1"
            .Cells(rw, ofb + 1) = Format(t_min - ws_corestaff.Range("D6"), "h")
            '.Range("A" & rw) = "CUL1"
            '.Range("B" & rw) = Format(t_min - ws_corestaff.Range("D6"), "h")
            rw = rw + 1
        End If
        If t_min >= ws_corestaff.Range("D7") And t_min <= ws_corestaff.Range("E7") Then
            .Cells(rw, ofb) = "HPE1"
            .Cells(rw, ofb + 1) = Format(t_min - ws_corestaff.Range("D7"), "h")
            '.Range("A" & rw) = "HPE1"
            '.Range("B" & rw) = Format(t_min - ws_corestaff.Range("D7"), "h")
            rw = rw + 1
        End If
        If t_min >= ws_corestaff.Range("D9") And t_min <= ws_corestaff.Range("E9") Then
            .Cells(rw, ofb) = "HPL1"
            .Cells(rw, ofb + 1) = Format(t_min - ws_corestaff.Range("D9"), "h")
            '.Range("A" & rw) = "HPL1"
            '.Range("B" & rw) = Format(t_min - ws_corestaff.Range("D9"), "h")
            rw = rw + 1
        End If
        If t_min >= ws_corestaff.Range("D11") And t_min <= ws_corestaff.Range("E11") Then
            .Cells(rw, ofb) = "RPE1"
            .Cells(rw, ofb + 1) = Format(t_min - ws_corestaff.Range("D11"), "h")
            '.Range("A" & rw) = "RPE1"
            '.Range("B" & rw) = Format(t_min - ws_corestaff.Range("D11"), "h")
            rw = rw + 1
        End If
        If t_min >= ws_corestaff.Range("D13") And t_min <= ws_corestaff.Range("E13") Then
            .Cells(rw, ofb) = "RPL1"
            .Cells(rw, ofb + 1) = Format(t_min - ws_corestaff.Range("D13"), "h")
            '.Range("A" & rw) = "RPL1"
            '.Range("B" & rw) = Format(t_min - ws_corestaff.Range("D13"), "h")
            rw = rw + 1
        End If
        If t_min >= ws_corestaff.Range("D15") And t_min <= ws_corestaff.Range("E15") Then
            .Cells(rw, ofb) = "WPE1"
            .Cells(rw, ofb + 1) = Format(t_min - ws_corestaff.Range("D15"), "h")
            '.Range("A" & rw) = "WPE1"
            '.Range("B" & rw) = Format(t_min - ws_corestaff.Range("D15"), "h")
            rw = rw + 1
        End If
        If t_min >= ws_corestaff.Range("D17") And t_min <= ws_corestaff.Range("E17") Then
            .Cells(rw, ofb + 1) = Format(t_min - ws_corestaff.Range("D17"), "h")
            .Cells(rw, ofb) = "WPL1"
            '.Range("B" & rw) = Format(t_min - ws_corestaff.Range("D17"), "h")
            '.Range("A" & rw) = "WPL1"
            rw = rw + 1
        End If
        
        'CELLS(ROW,COLUMN)
        lk = WorksheetFunction.CountIf(.Range(.Cells(2, ofb), .Cells(20, ofb)), d1 & "*")
        If lk = 2 Then                                                  'both early and late crews eligible
            If WorksheetFunction.VLookup(d1 & "E1", .Range(.Cells(2, ofb), .Cells(20, ofb)), 2, False) < WorksheetFunction.VLookup(d1 & "L1", .Range(.Cells(2, ofb), .Cells(20, ofb)), 2, False) Then
                crew_open = d1 & "E1"
            Else
                crew_open = d1 & "L1"
            End If
            .Cells(rw, ofb) = "SEC"
            .Cells(rw + 1, ofb) = "WlooPk"
        ElseIf lk = 1 Then
            With .Range(.Cells(2, ofb), .Cells(20, ofb))
                crew_open = .Find(What:=d1 & "*", After:=.Cells(.Rows.Count), LookIn:=xlValues, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Value
            End With
            .Cells(rw, ofb) = "SEC"
            .Cells(rw + 1, ofb) = "WlooPk"
        ElseIf lk > 2 Then
            l_diffa = WorksheetFunction.min(.Range(.Cells(2, ofb + 1), .Cells(20, ofb + 1)))
            l_diff = WorksheetFunction.Index(.Range(.Cells(2, ofb), .Cells(20, ofb)), WorksheetFunction.Match(l_diffa, .Range(.Cells(2, ofb + 1), .Cells(20, ofb + 1)), 0))
            crew_open = l_diff
            .Cells(rw, ofb) = "SEC"
        .Cells(rw + 1, ofb) = "WlooPk"
        Else
            'DO NOTHING
        End If
    End With
End Sub

Here is the data from worksheet ws_th:

Excel 2010
	A	B	C	D	E	F	G	H
1	HP OPEN		HP CLOSE		LP OPEN		LP CLOSE
2					CUE1	2
3					HPE1	2
4					HPL1	0
5					RPE1	2
6					WPE1	2
7					WPL1	2
8
9
10
11
12
13
14
15
16
17
18
19
20
TEMP_HOLD

When I get the error,

lk=2
d1="HP"
ofb=5

 

[mrmmickle1]

WorksheetFunction.VLookup(d1 & "E1", .Range(.Cells(2, ofb), .Cells(20, ofb)), 2, False)

If this is the table argument:

.Range(.Cells(2, ofb), .Cells(20, ofb))

it won't work because it is only in column 5 (ofb) Your Column Index is 2 which doesn't exist in a 1 column table.....

 

[Ark68]

Yup. That would do it.
I am just learning to adapt to using cells as an alternative to range. I had overlooked that that the two cell references only equated to one half of the vlookup range.

Thanks for the extra set of eyes!!

 

[mrmmickle1]

Glad to be of assistance.