How to Use VLOOKUP with VBA

15Aug

Using VLOOKUP within a VBA script can automate repetitive tasks or complex projects, enhancing efficiency and accuracy in data processing. Here’s a step-by-step guide on how to use VLOOKUP with VBA in Excel:

Table of Contents

Basic Concept

The VLOOKUP function in Excel searches for a value in the first column of a range or table and returns a value in the same row from a specified column. The syntax for VLOOKUP in Excel is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Using VLOOKUP in VBA

To use VLOOKUP in VBA, you will generally use the Application.WorksheetFunction.VLookup method.

Step-by-Step Guide

Open the VBA Editor: Press Alt + F11 to open the VBA Editor in Excel.
Insert a New Module: In the VBA Editor, right-click on any of the objects in the Project window, choose Insert, and then Module. This action creates a new module.
Write Your VBA Code: In the module window, you can write your VBA code. Here’s a simple example that uses VLOOKUP to find a value in a table on Sheet1 and outputs the result in a message box:


Sub UseVLookup()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

Dim lookupValue As Variant
Dim result As Variant
Dim tableRange As Range

lookupValue = "YourLookupValue" ' Change this to your lookup value
Set tableRange = ws.Range("A1:B10") ' Change this to your table range

' Attempt to find the lookup value in the table range
On Error Resume Next ' In case VLOOKUP does not find the value
result = Application.WorksheetFunction.VLookup(lookupValue, tableRange, 2, False)
On Error GoTo 0 ' Turn back on regular error handling

' Check if something was found
If IsError(result) Then
MsgBox "Value not found!", vbExclamation
Else
MsgBox "The value is: " & result, vbInformation
End If
End Sub

In this script:

lookupValue is the value you’re searching for in the table.
tableRange is the range of cells that makes up your table. VLOOKUP will search for lookupValue in the first column of this range.
The VLookup function is used to search for lookupValue within tableRange, aiming to return a value from the second column (col_index_num = 2) where an exact match is specified (False for the range_lookup).
Error handling is used to manage cases where the value is not found.

Executing the Macro

After writing your code, you can run your macro by pressing F5 while in the VBA Editor with your subprocedure selected, or you can assign the macro to a button in your Excel sheet for easier access.

Customizing Your VBA VLOOKUP

This basic example can be expanded or modified to suit your needs. For instance, you might loop through a range of cells, applying VLOOKUP to each one, or you might incorporate VLOOKUP into more complex data processing operations within your VBA script.

Remember, VBA and functions like VLOOKUP are powerful tools that, when used together, can significantly enhance your ability to manipulate and analyze data in Excel.

Advanced Example: Looping Through a Range

Here’s an example that loops through a range of cells in Column A and performs VLOOKUP for each cell:


Sub VLookupLoop()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim lookupRange As Range
    Set lookupRange = ws.Range("A2:A10") ' Range to loop through
    
    Dim cell As Range
    Dim result As Variant
    Dim tableRange As Range
    
    Set tableRange = ws.Range("B2:C10") ' Change this to your table range
    
    For Each cell In lookupRange
        On Error Resume Next
        result = Application.WorksheetFunction.VLookup(cell.Value, tableRange, 2, False)
        On Error GoTo 0
        
        If IsError(result) Then
            cell.Offset(0, 1).Value = "Not Found"
        Else
            cell.Offset(0, 1).Value = result
        End If
    Next cell
End Sub

Best Excel Tutorial