[Excel VBA] How to Search for Values in a Table (ListObject)

This article introduces how to use Excel VBA to search for cells in a table that match specified conditions and retrieve their values. This procedure allows you to efficiently extract data that meets specific criteria from tables containing large amounts of data.

1. Preparation

• Table: Prepare the data to be searched in a table format. Here, we configure the data as an Excel Table (ListObject).
• Search Conditions: Use values specified in cells or ranges as search criteria.

2. Basic Table Search Method in VBA

The following code explains how to search for specific rows and columns and retrieve their values.

Basic Code

Below is sample code that retrieves a value from an Excel sheet table based on specified row and column criteria.

Sub SearchTableValue()
    Dim tableName As String
    Dim rowNum As Variant
    Dim colNum As Variant
    Dim resultValue As Variant
    Dim tableRange As ListObject
    Dim rowDataRange As Range
    Dim headerRange As Range

    ' Set table name (Change "MyTable" to your actual table name)
    tableName = "MyTable"
    Set tableRange = ThisWorkbook.Sheets("Sheet1").ListObjects(tableName)

    ' Get row and column search values from the sheet
    Dim searchRowValue As Variant
    Dim searchColValue As Variant
    searchRowValue = Sheets("Sheet1").Range("M3").Value ' Search value for row direction
    searchColValue = Sheets("Sheet1").Range("N3").Value ' Search value for column direction

    ' Set the 1st column of the table as the target for row search
    Set rowDataRange = tableRange.ListColumns(1).DataBodyRange

    ' Set the 3rd row of the table data part as the target for column search
    Set headerRange = tableRange.HeaderRowRange.Offset(2) ' Set target to 2 rows below the header

    ' Search for the M3 value in the 1st column and get the matching row number
    On Error Resume Next
    rowNum = Application.WorksheetFunction.Match(searchRowValue, rowDataRange, 0)
    On Error GoTo 0
    If IsError(rowNum) Then
        MsgBox "Row not found."
        Exit Sub
    End If

    ' Search for the N3 value in the 3rd row of the data part and get the matching column number
    On Error Resume Next
    colNum = Application.WorksheetFunction.Match(searchColValue, headerRange, 0)
    On Error GoTo 0
    If IsError(colNum) Then
        MsgBox "Column not found."
        Exit Sub
    End If

    ' Get value from row and column positions
    resultValue = tableRange.DataBodyRange.Cells(rowNum, colNum).Value
    MsgBox "Search Result: " & resultValue
End Sub

Explanation of the Code

Set the Table Set tableRange = ThisWorkbook.Sheets("Sheet1").ListObjects(tableName) Identify the table to be searched. In this code, the table name is specified in the tableName variable.

Row Direction Search rowNum = Application.WorksheetFunction.Match(searchRowValue, rowDataRange, 0) The Match function is used for the row search. It retrieves the row number that matches the value searchRowValue (set in cell M3) from the first column of the table (rowDataRange).

Column Direction Search colNum = Application.WorksheetFunction.Match(searchColValue, headerRange, 0) The Match function is also used for the column search. It retrieves the column number that matches the value searchColValue (set in cell N3) from the specified row within the table data (headerRange).

Display Search Result resultValue = tableRange.DataBodyRange.Cells(rowNum, colNum).Value The code retrieves the value from the position identified by the row and column numbers and displays the result using MsgBox.

3. Application: Displaying the Value in Another Cell

It is also possible to display the retrieved value in a specific cell on the sheet instead of a MsgBox. The following code displays the search result in cell V3.

Sheets("Sheet1").Range("V3").Value = resultValue

Summary

By using this code, you can easily search for and utilize table values that match specific conditions in Excel VBA. This method is very useful when searching through large amounts of data or when multiple conditions are required. Please try this when you want to streamline data processing within tables.