Vlookup Searching All Entries Of A Lookup Table
For the most fastest and most advanced usages of the VLookup command, Rich's new book provides both detailed instructions and a downloadable app that includes fully functional code examples.
Power-Up Using Excel VBA Sorts and Searches
This example shows how to use vlookup to search for an exact match in a translation table that is not sorted.
Program Code
Option Explicit
Option Base 1
Sub VlookupTestAndTiming()
Dim rngAnswerRange As Range
Dim C As Range
Dim i As Long
Dim intSearchForMe As Long
Dim varAnswerFound As Variant
Dim rngTableArray As Range
Dim dteStartTime As Single
Dim dteEndTime As Single
' ****************************************************************
' Initialize The Ranges
' ****************************************************************
Set rngAnswerRange = Range("B1:B20000")
Set rngTableArray = Range("A1:B20000")
If MsgBox("Initialize Rows?", vbYesNo) = vbNo Then
GoTo Skip_Initialize
End If
' ****************************************************************
' Initialize The First Column Using Cells Method
' ****************************************************************
For i = 1& To 20000
Cells(i, 1&).Value = i
Next i
' ****************************************************************
' Initialize The Second Column Using The Range Method
' ****************************************************************
For Each C In rngAnswerRange
C.Value = C.Offset(0, -1).Value * 2
Next C
Skip_Initialize:
' ****************************************************************
' Exercise The Vlookup
' ****************************************************************
dteStartTime = Timer
On Error Resume Next
For i = 1 To 20000
intSearchForMe = Cells(i, 1&).Value
' ****************************************************************
' Vlookup Parameters
' (1) The Value You Are Looking For
' (2) The Range Of The Values Table You Are Searching
' (3) The Column Number of the Value Table That Contains the "Answer"
' (4) False - Find An Exact Match - Value Table Does Not Need To Be
' In Order
' ****************************************************************
varAnswerFound = Application.WorksheetFunction.VLookup(intSearchForMe, rngTableArray, 2, False)
If Err.Number = 1004 or Err.Number = 438 Then
Err.Clear
MsgBox ("Error At Row " & i)
Exit Sub
End If
Next i
dteEndTime = Timer
MsgBox ("Time To Run = " & dteEndTime - dteStartTime)
On Error GoTo 0
End Sub