Find function to locate result of a formula in a cell

Question

In order to return the cell reference, I'm attempting to search across column F for the number 9999.

Only when the number 9999 is entered into column F does my code provide the cell where this number is situated.

Formula-driven column F yields the result 9999 as the result of the formula's reasoning.

Dim foundRng as Range
Set foundRng = Range ("F1:F2000").Find("9999")
If foundRng is Nothing then
    MsgBox "Value not found"
Else
    MsgBox foundrng.Address
End if
End Sub

Answer 1

There are several attributes to the Find function. The majority of these are automatically set by Excel based on the most recent use. Excel will remember your most recent option even if you last used Find manually. Therefore, while using VBA, all pertinent properties should be set.

Sub FindCell()

    Dim foundRng As Range
    
    Set foundRng = Range("F1:F2000").Find(What:="9999", _
                                          After:=Cells(2000, "F"), _
                                          LookIn:=vbValues, _
                                          LookAt:=xlWhole, _
                                          SearchOrder:=xlByRows, _
                                          SearchDirection:=xlNext, _
                                          MatchCase:=False)
    If foundRng Is Nothing Then
        MsgBox "Value not found"
    Else
        MsgBox foundRng.Address
    End If

The LookIn property, which I believe your system currently uses xlFormulas (it's xlValuesabove), is the most likely cause of your issue. The formula and value of a cell are the same when it contains a hard value, such as 9999. However, if the formula is =3*3333, the value and the formula diverge, and the number 9999 is not found in the formula.