title: Alternative to Vlookup in VBA?
body: <p>A strange question perhaps, but is there an alternative way of opening a workbook, searching for a particular reference in a column, and then pulling the data from a another column in that row using VBA, without using VLookup?</p>
  
  <p>The table I am trying to get data from contains a mixture of numbers, text, dates, and the lookup value is often >13 digits long.</p>
  
  <p>I sort of had something working with VLookup, but it was too inconsistent - every so often it would just break because the data type didn't match. An awful lot of 'type mismatch' or 'ByRef' errors - I'd get one right and then another breaks.</p>
  
  <p>Unfortunately I don't know enough to know what to search to get me in the right direction.</p>
  
  <p>If it helps explain what I'm trying to do, here's my code using VLookup that errors all the time:</p>
  
  <pre><code>Sub getData()
  
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Application.Calculation = xlManual
  
  Dim wb As Workbook, src As Workbook
  Dim srcRange As Range
  Dim InputString
  Dim strStatus
  Dim strStatusNum
  Dim strD1
  Dim I As Integer
  
  Set wb = ActiveWorkbook
  
  I = 7
  
  Set src = Workbooks.Open("D:\Files\test2.xlsx", True, True)
      With src.Sheets(1)
          Set srcRange = .Range(.Range("A1"), .Range("H1").End(xlDown))
  End With
  
  Do While wb.ActiveSheet.Cells(I, 1) &lt;&gt; ""
  
      'Makes sure src.Close is called if errors
      'On Error Resume Next
  
      InputString = wb.Worksheets("Sheet 1").Cells(I, 1)
  
      strStatus = Application.VLookup(InputString, srcRange, 3, False)
  
      strD1 = Application.VLookup(InputString, srcRange, 4, False)
  
      'Convert strStatus to actual number e.g. "03. no d1"
      strStatusNum = Left(strStatus, 2)
  
      wb.Worksheets("Sheet 1").Cells(I, 4) = strStatusNum
  
          If (strStatusNum &lt;&gt; 3) Then
  
              wb.Worksheets("Sheet 1").Cells(I, 2) = "Not at 03. No Work Order"
  
          ElseIf (strStatusNum = 3) And (strD1 &lt;&gt; "") Then
  
              wb.Worksheets("Sheet 1").Cells(I, 2) = "D1 Received"
              wb.Worksheets("Sheet 1").Cells(I, 3) = strD1
  
          Else
  
              wb.Worksheets("Sheet 1").Cells(I, 2) = "No D1"
  
          End If
  
      I = I + 1
  
  Loop
  
  
  src.Close (False)
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  Application.Calculation = xlAutomatic
  
  End Sub
  </code></pre>
  
  <p>EDIT: Corrected some syntax.</p>