Find address of a cell containing a formula, using text value in the cell

Question

I am trying to find a cell line containing the word "ok", this is my code:

Dim findValue As Long
findValue = Sheets("STOCKS").Range("E:E").Find("ok").Row

The problem is that the cell containing the word "ok", for example E5, is filled by a formula taking the value of another cell in another sheet. So I am getting the following error:

execution error 91 : object variable or bloc variable with not defined
(I have translated the message from French to English.)

I think it's because the cell contains the formula and not the real value.

Answer 1

If you really think the formula is the problem you can add

lookin:=xlValues

as a parameter to your find function so that it reads:

findValue = Sheets("STOCKS").Range("E:E").Find("ok", lookin:=xlValues).Row

You will also get this error if the value you're looking for isn't found. You can check for this with this code.

Dim findValue As Long
Dim r As Range

Set r = Sheets("STOCKS").Range("E:E").Find("ok", LookIn:=xlValues)

If r Is Nothing Then
    MsgBox "Not found"
    findvalue = -1
Else
    findvalue = r.Row
End If