Excel VBA range.find acting up

vba excel visual-studio-macros
Tyler Durden picture Tyler Durden · Jan 28, 2014 · Viewed 17k times · Source

Im trying to use range.find to look up a value within a column, and return the matching value from the next column.

I recorded the find() using the macro recorder, which seemed to work fine for a while, but for some reason it's now giving me an error. As far as I can tell I haven't changed anything that should affect this bit of code.

This is what I have

Public Function look_up_id(id, table)
    Worksheets(table).Activate
    Cells.Find(What:=id, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

    look_up_id = ActiveCell.Offset(0, 1).Value
End Function

The error I'm now getting is:

Object variable or With block variable not set

Any idea why this is now happening?

All the resources I can find on range.find() look like I'm doing it right...

Cheers - David

Answer

Dmitry Pavliv picture Dmitry Pavliv · Jan 28, 2014

Try to use this code instead (when Find doesn't find anything, it returns Nothing and then you tried to do sth like this Nothing.Activate, and this triggers an error):

Dim res As Range

Set res = Worksheets(table).Cells.Find(What:=id, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
If Not res Is Nothing Then
    look_up_id = res.Offset(0, 1).Value
End If

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More