Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,665
- Office Version
- 365
- 2016
- Platform
- Windows
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?
Here is the data from worksheet ws_th:
When I get the error,
lk=2
d1="HP"
ofb=5
Rich (BB code):
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