VBA & Macro Problems

How to Find Multiple Matches Across Excel Worksheets with VBA

Nimra Malik Oct 22, 2026 869 views

Question details

The user wants to write a VBA macro to search every worksheet for a specific identifier (like a PSID) and return all matching sales values, while avoiding runtime errors associated with uninitialized arrays and missing ranges.

How to Find Multiple Matches Across Excel Worksheets using VBA

Product: Excel
Device & OS: not provided
Scenario: Running a VBA script to aggregate data by searching for a specific value across multiple sheets and appending all matches into an array or output range.
Observed behavior: Scripts fail when UBound is called on a non-array value (if only a single Variant is used) or attempt to reference missing ranges, causing errors during the Find loop.

Before you start

Ensure your workbook is saved in a macro-enabled format (.xlsm) and that you have enabled Developer options to access the VBA editor safely.

Solution 1Recommended

Use a Dynamic Array with Proper Loop Exits

Create a macro that searches each worksheet using a dynamic array, ensuring you safely handle ranges that return as 'Nothing' to prevent runtime errors.

When collecting multiple instances across sheets, use the `Find` and `FindNext` methods within a loop. It is crucial to check if the range actually exists using `If Rng Is Nothing Then Exit Do` (or `Exit For` depending on your loop) before attempting to store the value.

Open the VBA Editor

Press Alt + F11 to open the VBA Editor, then go to Insert > Module to create a new script area.

Define Your Variables

Declare a dynamic array (e.g., `Dim Results() As Variant`) to store your matching values, along with your Range and Worksheet variables.

Loop Through All Worksheets

Start a loop through the workbook using `For Each ws In ThisWorkbook.Worksheets` to ensure every sheet is searched.

Execute the Find Method

Use the `Find` method on your target column (e.g., Column A). If a match is found, store its first address (e.g., `FirstAddress = Rng.Address`) to prevent an infinite loop.

Implement the Loop Exit Check

Use a `Do...Loop` with `FindNext`. Add the critical line `If Rng Is Nothing Then Exit Do` immediately after the Find operation to safely exit when no more matches exist on that sheet.

Store the Results

Use `ReDim Preserve` to increase the size of your dynamic array and append the matched sales value from the required column.

Use a Dynamic Array with Proper Loop Exits

Verify Array Population: Always check whether a match was actually found before calling UBound on your results array to prevent 'Type Mismatch' or 'Subscript out of range' errors.

Solution 2

Store and Output Multiple Columns using a 2D Array

If you need to retrieve multiple columns of data for each match, utilizing a two-dimensional array is the most efficient approach.

Advanced Data Handling

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros, allowing you to write, edit, and run scripts to find multiple matches across worksheets just like you would in Microsoft Excel.

1. Download and Install WPS Office: Get the latest version of WPS Office and open your .xlsm file in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on 'VBA Editor' to open the script environment.
3. Run Your Macro: Paste your cross-worksheet search code, ensure your loops are properly structured, and click Run to aggregate your data.

High compatibility with Microsoft Excel VBA syntax and objectsLightweight application that handles large macro operations smoothlyFamiliar developer interface to manage modules, arrays, and scripts easily

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Subscript out of range' error when calling UBound?

This error occurs if the dynamic array was never initialized, typically because the Find method didn't locate any matches. To fix this, always include a boolean flag or an error handling check to verify that matches were found before executing the UBound function.

Should I use 'Exit For' or 'Exit Do' when checking 'If Rng Is Nothing'?

The correct exit statement depends on the loop containing your Find operation. If you are searching within a 'Do...Loop' (which is the standard approach for FindNext operations), you must use 'Exit Do'. If you are iterating using a 'For...Next' loop, use 'Exit For'.

How do I prevent an infinite loop when using FindNext in VBA?

When using FindNext, the search will loop back to the beginning of the sheet once it hits the end. To prevent an infinite loop, record the address of the very first match (e.g., `FirstAddress = Rng.Address`). Inside your loop, check if the address of the newly found range matches `FirstAddress`. If it does, exit the loop.

Excel & Data Problems