How to Fix Runtime Error 91 in Excel VBA When Using the Find Function

Learn how to correctly use the Find function in Excel VBA to avoid `Runtime Error 91: Object Variable or With block variable not set`.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Find Not Returning Value Even When Present

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding and Fixing Runtime Error 91 in VBA

When working with Excel VBA, you may encounter a frustrating issue: Runtime Error 91: Object Variable or With block variable not set. This error can occur while using the Find function, particularly when you're trying to locate a value in a specific range. Although you may be certain that the value exists, the error can appear due to improper syntax in your code. In this post, we’ll delve into this issue, explain why it happens, and provide a clear solution.

The Problem: Finding a Value in a Column

Imagine you are tasked with locating a specific value in a column and then adding a new row directly beneath the last row of existing data. You might assume this process is straightforward, but encountering Runtime Error 91 can be frustrating, especially when you are confident that the value exists in the specified range. The code snippet below illustrates the typical setup that leads to this error:

[[See Video to Reveal this Text or Code Snippet]]

The Core Issue

The error arises because the Find function may return Nothing if it does not find the specified value, leading to an attempt to access an attribute of an uninitialized object. This behavior is what triggers Runtime Error 91, leaving you stuck without a solution.

The Solution: Proper Use of the Set Keyword

The obstacle in handling this error lies in how you assign a value to an object variable in VBA. To fix this issue, we need to ensure that we are using the Set keyword when assigning an object reference. The corrected code should look like this:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of Changes Made:

Use of Set: By using the Set keyword when assigning the result of Find, we clarify to VBA that FirstRow is an object variable.

Nil Check: Including a check for Nothing ensures that your code will handle the case where the SearchTerm is not found. By adding this check, you can provide feedback to the user, like displaying a message box.

Why the Change Matters

Understanding and correctly applying the Set keyword is crucial because:

It prevents runtime errors by ensuring that object variables are properly assigned.

It improves your code's robustness by handling cases where the search term isn't present, allowing for a better user experience.

Conclusion

Handling Runtime Error 91 in Excel VBA doesn’t need to be an insurmountable challenge. By ensuring that you use Set to assign object variables and properly checking if a value was found before proceeding, you can avoid misleading error messages and create smoother, more reliable code. Next time we're searching for a value in a column, keep these adjustments in mind, and you'll be set for success!