Find Result is Inconsistent

Question

I have this Excel VBA code.

Sub setEVAFieldValue(pattern As String, fval As Variant)
    Dim lrange As Range
    Dim lrange2 As Range
    Dim lrange3 As Range
    Dim rowpos As Integer
    Dim colpos As Integer

    Set lrange = Worksheets("EVA").Cells.Find(pattern)
    If (lrange.Row > 0) Then
        rowpos = lrange.Row
        colpos = lrange.Column
        Worksheets("EVA").Cells(rowpos, colpos + 2).value = fval
    End If
End Sub

This normally finds the cell with the pattern and adds the values to the adjacent cell.

But when another piece of code in the worksheet does some calculations and shows a form with these calculations, the find does not work any more. It returns nothing for the range, even if I see the cell in the worksheet with the pattern.

Example of a pattern: "Beta"

The call used: Call setEVAFieldValue("Beta", betaValue)

The value in the cell: "5) Beta"
The value in the cell is without the double quotes

When using `Find`, you should always specify the `What`, `LookIn`, and `LookAt` parameters. You should also [test if the Find succeeded](https://stackoverflow.com/questions/1589939/how-to-detect-whether-vba-excel-found-something). — BigBen, Oct 31 '22 at 14:42
Btw, the `.Row` of a `Range` object will always be `>0` (minimum is 1). — BigBen, Oct 31 '22 at 14:47
Hmm. Can you add an example of the pattern you're using, and the cell value that matches the pattern? Does your other code perform a `Find` or maybe a `Replace`? — BigBen, Oct 31 '22 at 15:03
Can you also add the other `Find`? From the [docs](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find): The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method. — BigBen, Oct 31 '22 at 15:08
thank you it seems to have worked, i added the LookAt:=xlPart and seems to have fix it. did no know the find parameters would be overwriten — rread, Oct 31 '22 at 15:19
@BIgBen it would be cool if you at that last comment as answer so you can get the recognition for this. — rread, Oct 31 '22 at 15:22

score 2 · Accepted Answer · answered Oct 31 '22 at 15:41

From the Range.Find docs:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Said otherwise, when using Find, always specify the What, LookIn, and LookAt parameters (and SearchOrder and MatchByte if they are relevant to the find you're performing).

Find Result is Inconsistent

1 Answers1