How to Fix VBA Run-Time Error 91 with Find and Offset in Excel
Question details
The user is experiencing Run-Time Error 91 in VBA when using the Find and Offset methods together during a list-box double-click event to populate form controls.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to locate a selected ID in a worksheet using the VBA Find method and returning an offset value to populate user form controls.
- Observed behavior
- Chaining .Find().Offset() causes Run-Time Error 91 ("Object variable or With block variable not set") when the Find method fails to locate the specified value and returns Nothing.
Verify that your target worksheet and data ranges are properly referenced in your script, and ensure you have enabled Option Explicit to catch undeclared variables.
Separate Find and Offset to Check for 'Nothing'
Prevent Error 91 by assigning the Find result to a Range variable and verifying it is not empty before applying Offset.
When you chain methods like Sheet2.Range().Find().Offset(), VBA throws Error 91 if the Find method fails. This is because Find returns 'Nothing' when a match isn't located, and you cannot apply the Offset property to an empty object.
Breaking the statement into distinct parts allows you to validate the result before continuing the script execution.
At the top of your subroutine, declare a dedicated variable for the search result using 'Dim findvalue As Range'.
Assign the search result to your variable without chaining Offset: 'Set findvalue = Sheet2.Range("H:H").Find(What:=ID, LookIn:=xlValues)'.
Add an IF statement to check if the value was found: 'If findvalue Is Nothing Then MsgBox "No matching record was found" Exit Sub End If'.
Once validated, you can safely apply the offset to your variable: 'Set findvalue = findvalue.Offset(0, -6)'.
Optimize ListBox Loop and Add Error Handling
Improve the double-click event by exiting the loop immediately once the item is found, validating the ID, and wrapping the procedure in an error handler.
Use WPS Spreadsheet for Seamless Macro Integration
WPS Office provides exceptional compatibility with standard VBA scripts and macros. Easily write, edit, and debug your VBA code with an intuitive built-in editor that supports the Find and Offset methods flawlessly.
- 1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your existing macro-enabled document.
- 2. Access the Developer tab: Navigate to the 'Developer' tab on the ribbon to access macro tools.
- 3. Open the Visual Basic Editor: Click 'Visual Basic' to open the built-in VBA editor.
- 4. Update your code: Implement the Find and Offset validation checks in your ListBox code.
- 5. Run and test: Run your macro directly from the editor to confirm Error 91 is resolved.
Frequently Asked Questions
What does Run-Time Error 91 mean in VBA?
Run-Time Error 91, "Object variable or With block variable not set", occurs when you attempt to use an object variable that has not been initialized or assigned to a valid object, such as when a Find method fails and returns 'Nothing'.
Why does adding Offset to Find cause an error?
If the Find method cannot locate your specified value, it evaluates to 'Nothing'. Because 'Nothing' is not a valid cell range, attempting to call the Offset property on it triggers Error 91.
How do I handle empty selections in a VBA ListBox?
Instead of checking if lstLookup.Selected(I) = "", which is incorrect because Selected returns a Boolean, retrieve the list value into a String variable and check its length using 'If Len(ID) = 0 Then'.
What is the purpose of Option Explicit in VBA?
Option Explicit forces you to declare all variables before using them (e.g., using Dim). This helps prevent typos and makes troubleshooting object-related errors like Error 91 much easier by alerting you to undeclared variables at compile time.
