title: VLookup in VBA is not working however it's working on cells
body: <p>This is my function that to take an array with dates and return the hours per week according to an existing table in my Sheet <code>Eng_Availability_Report</code>. For me, it seems to be correct but VBA sends me a message with Error 1004. I used a <code>MsgBox</code> to return the weeknumber and the number actual exists in the range I'm looking for.</p>
  
  <pre><code>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 (" " &amp; WeekNumber &amp; " ")
          If WeekNumber &gt; 0 And WeekNumber &lt; 14 Then
              Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("F6:G19"), 7, False)
          ElseIf WeekNumber &gt; 14 And WeekNumber &lt; 27 Then
              Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("H6:I19"), 9, False)
          ElseIf WeekNumber &gt; 27 And WeekNumber &lt; 40 Then
              Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("J6:K19"), 11, False)
          ElseIf WeekNumber &gt; 40 And WeekNumber &lt;= 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 &gt; 0 And WeekNumber &lt; 14 Then
              Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("F6:G19"), 7, False)
          ElseIf WeekNumber &gt; 14 And WeekNumber &lt; 27 Then
              Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("H6:I19"), 9, False)
          ElseIf WeekNumber &gt; 27 And WeekNumber &lt; 40 Then
              Hours(k) = Application.WorksheetFunction.VLookup(WeekNumber, ThisWorkbook.Sheets("Eng_Availability_Report").Range("J6:K19"), 11, False)
          ElseIf WeekNumber &gt; 40 And WeekNumber &lt;= 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
  </code></pre>