VBA 中的 VLookup 不起作用，但它正在处理单元格

Question

这是我的函数，用于获取一个带有日期的数组，并根据我的工作表Eng_Availability_Report中的现有表返回每周的小时数。对我来说，这似乎是正确的，但VBA向我发送了一条错误为1004的消息。我使用了一个MsgBox来返回周数，而实际数字存在于我要查找的范围内。

Public Function ReturnHoursPerWeek(Arr1() As Variant) As Variant
Dim Hours() As Double, k As Integer, WeekNumber As Integer
Dim ws As Worksheet, wb As Workbook
k = 1
Set wb = ThisWorkbook
Set ws = wb.Sheets("Eng_Availability_Report")
If LBound(Arr1()) = UBound(Arr1()) Then
ReDim Hours(LBound(Arr1()))
WeekNumber = Int(((Arr1(1, 1) - DateSerial(Year(Arr1(1, 1)), 1, 0)) + 6) / 7)
MsgBox (" " & WeekNumber & " ")
If WeekNumber > 0 And WeekNumber < 14 Then
Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("F6:G19"), 7, False)
ElseIf WeekNumber > 14 And WeekNumber < 27 Then
Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("H6:I19"), 9, False)
ElseIf WeekNumber > 27 And WeekNumber < 40 Then
Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("J6:K19"), 11, False)
ElseIf WeekNumber > 40 And WeekNumber <= 53 Then
Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("L6:M19"), 13, False)
End If
Else
ReDim Hours(LBound(Arr1()) - UBound(Arr1()))
For i = LBound(Arr1()) To UBound(Arr1())
WeekNumber = Int(((Arr1(i, 1) - DateSerial(Year(Arr1(i, 1)), 1, 0)) + 6) / 7)
If WeekNumber > 0 And WeekNumber < 14 Then
Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("F6:G19"), 7, False)
ElseIf WeekNumber > 14 And WeekNumber < 27 Then
Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("H6:I19"), 9, False)
ElseIf WeekNumber > 27 And WeekNumber < 40 Then
Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("J6:K19"), 11, False)
ElseIf WeekNumber > 40 And WeekNumber <= 53 Then
Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("L6:M19"), 13, False)
End If
k = k + 1
Next
End If
ReturnHoursPerWeek = Hours()
End Function

Answer 1

查看您的代码，当您只有2列(F:F-G:G)时，您似乎在寻找列号，例如7。我想你需要改变范围或减少增量。

目前，您正在寻找数组之外的列。根据你可能想从根"匹配"单元格开始的字母，向右走7/8/9等，然后返回值。我假设(根据代码)"匹配单元格"在第一列，因此通过更改范围，使第一列始终为A，以您想要的列结束。这应该可以解决问题。