title: VBA Match Function with Array
body: <p>when running below code I get "Object Required" error on the last line in the code. I cant really figure out why.</p>
  
  <pre><code>Dim FirstCurrentRange As Range
  Dim FirstRowCounter As Long
  Dim FirstArrayCounter As Variant
  Dim FirstCurrentArray() As Variant
  
  Dim ColumnCounter As Long
  Dim ArrayCounter As Variant
  Dim CurrentArray() As Variant
  Dim SiteNameMatch As Long
  
  Set FirstCurrentRange = ThisWorkbook.Worksheets(InputSheet.Name).ListObjects("DataTable").Range
  FirstArrayCounter = 0
  
  For FirstRowCounter = 2 To FirstCurrentRange.Rows.Count
      FirstArrayCounter = FirstArrayCounter + 1
      ReDim Preserve FirstCurrentArray(1, FirstArrayCounter)
      FirstCurrentArray(1, FirstArrayCounter) = FirstCurrentRange(FirstRowCounter, 4).Value
  Next FirstRowCounter
  
  ThisWorkbook.Worksheets(DeleteMe.Name).Range("A1:A" &amp; FirstArrayCounter).Value = Application.WorksheetFunction.Transpose(FirstCurrentArray)
  
  ReDim Preserve CurrentArray(2, FirstArrayCounter)
  ArrayCounter = 0
  
  For ColumnCounter = LBound(FirstCurrentArray, 2) To UBound(FirstCurrentArray, 2)
  
  SiteNameMatch = Application.WorksheetFunction.Match(FirstCurrentArray(1, ColumnCounter).Value, ThisWorkbook.Worksheets(ContactListSheet.Name).ListObjects("ContactTable").ListColumns("Site").DataBodyRange, 0)
  </code></pre>