VBA & Macro Problems

How to Fix VBA Runtime Error 91 When a Worksheet Cell is Blank

Camila Milosovich Oct 16, 2026 870 views

Question details

The user needs to prevent a runtime error 91 in a VBA procedure that is triggered by a Worksheet_Change event when a specific target cell is left blank.

How to Fix VBA Runtime Error 91 When a Worksheet Cell is Blank

Product: Spreadsheet
Device & OS: not provided
Scenario: Running a VBA macro containing a Find loop triggered by a Worksheet_Change event, particularly when the search reference cell (e.g., B2) is cleared by the user.
Observed behavior: The macro throws runtime error 91 at the rng.Address check because the Find operation returns Nothing. Additionally, the Worksheet_Change event triggers recursively and causes a crash.

Before you start

Ensure you have saved a backup of your workbook with the .xlsm extension before modifying the VBA code to prevent accidental loss of your macro scripts.

Solution 1Recommended

Add an Error Check for the 'Nothing' Object

Prevent the runtime error by explicitly checking if the range object exists before attempting to access its properties.

When a cell like B2 is blank, the Find method fails to locate a match and assigns 'Nothing' to your range variable. Trying to read the Address property of 'Nothing' directly causes runtime error 91.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor.

Locate the macro code

Find the module or worksheet containing your Find loop (e.g., the ListFoundItems procedure).

Insert the verification statement

Immediately after the Find execution and before the 'Loop Until rng.Address = adr' statement, add the following line: 'If rng Is Nothing Then Exit Do'.

Save and test

Save your macro and clear cell B2 in your worksheet to verify that the procedure now exits gracefully without throwing an error.

Add an Error Check for the 'Nothing' Object

Best Practice: Always validate the result of a Find method by checking 'If Not rng Is Nothing Then' before manipulating the returned range.

Solution 2

Disable Application Events to Prevent Infinite Loops

Stop the Worksheet_Change event from recursively calling itself when the macro modifies cells on the worksheet.

Advanced VBA Support in WPS Office

Write and Debug VBA Macros Seamlessly in WPS Office

WPS Office provides robust built-in support for VBA macros in its Spreadsheet application. You can easily run, edit, and debug your existing Excel scripts without any modification.

1. Download and Install: Download WPS Office from the official website and complete the installation process.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the macros.
3. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' to open the VBA Editor.
4. Apply the Fix: Implement the 'If rng Is Nothing Then Exit Do' fix in your script and run your macro seamlessly.

Fully compatible with Microsoft Excel VBA code and .xlsm formatsBuilt-in VBA Editor for seamless script modification and debuggingLightweight application that runs complex macros quickly and efficientlyFree and user-friendly interface for immediate productivity

microsoft office alternative - wps office

Frequently Asked Questions

What causes runtime error 91 in VBA?

Runtime error 91 occurs when you try to use an object variable that has not been set or has returned 'Nothing'. In this scenario, it happens when attempting to read the address of a failed Find search result.

Why does my Worksheet_Change event crash the spreadsheet?

If your VBA code modifies cells within a Worksheet_Change event, it triggers the event again, creating an infinite loop. Disabling Application.EnableEvents temporarily prevents this recursive behavior.

How do I handle empty cells when using the VBA Find method?

You should always check the result of the Find method using an 'If Not rng Is Nothing Then' statement before proceeding with actions on the found range. This ensures your code gracefully handles scenarios where the search text is empty or missing.

Excel & Data Problems