CODEWITHSUNDEEP
excelvba
Diti
Diti

Reputation: 1

Find function to locate result of a formula, in a cell

I'm trying to look into column F and find number 9999 to return the cell reference.

My code returns the cell where this number is located only if the number 9999 is typed into column F.

Column F is formula driven and the number 9999 is the outcome of the logic behind the formula.

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

Upvotes: 0

Views: 284

Answers (1)

Variatus
Variatus

Reputation: 14373

The Find function has many properties. Most of them are set by Excel automatically, depending upon last use. Even if you last used Find manually, Excel will remember your last setting. Therefore you should set all relevant properties when using VBA.

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 likely source of your problem is the LookIn property which I suspect your system currently uses xlFormulas (it's xlValuesabove). When a cell has a hard value, like 9999, its formula and value are the same. But if the formla is =3*3333 value and formula differ and 9999 will not be found in the formula.

The other item worth mentioning is the After property. Excel starts the search in the cell after the specified cell (by default the first cell in the range). The next cell after is specified by the SearchDirection property (above xlNext, could be xlPrevious). The function will loop through the entire range. Therefore, by specifying the last cell as after and xlNext as direction, the search will actually start in the first cell whereas, if the first cell is specified as After it will, in fact, be the last cell to be examined.

Upvotes: 1

Related Questions