CODEWITHSUNDEEP
excelvbafind
rread
rread

Reputation: 173

Find Result is Inconsistent

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

Upvotes: 2

Views: 65

Answers (1)

BigBen
BigBen

Reputation: 49998

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).

Upvotes: 2

Related Questions