CODEWITHSUNDEEP
excelvba
richard
richard

Reputation: 21

excel vba - range.find method

i have a problem with the following code, the wrong row is returned based on the string searched

PlayerName always exists and is unique with the named range EventString

but

cell x containing string "tt" will return row number 26 - first row of named range - incorrect

same cell x containing string "ttt" will return row number 35 - correct

Private Sub TextBox10_Change()

Dim PlayerName As String
Dim RowNumber As Integer

PlayerName = Label19.Caption

RowNumber = Range(EventString).Find(PlayerName, , Excel.xlValues).Row
Worksheets(wsEclec).Cells(RowNumber, 117).Value = TextBox10.Value

End Sub

any help would be much appreciated

richard

edit :

"tt" exists but contents of textbox10 are written to cells "26,117"

if I change "tt" to "ttt" contents of textbox10 are written to cells "35,117" the searched table

edit 2 : having posted the picture i've just spotted what's happening

range.find is finding the first partial match so i somehow need to specify an exact match

Upvotes: 1

Views: 2092

Answers (2)

richard
richard

Reputation: 21

thanks all - scott -

i made a bad assumption that .find was trying to find an exact match

this change has done it

RowNumber = Range(EventString).Find(PlayerName, LookIn:=xlValues, LookAt:=xlWhole).Row

Upvotes: 1

Sixthsense
Sixthsense

Reputation: 1975

Use a variable to check whether the search text is found or not like the below.

Private Sub TextBox10_Change()
Dim PlayerName As String, RowNumber As Integer, rfound As Range

PlayerName = Label19.Caption
Set rfound = Range(EventString).Find(PlayerName, , Excel.xlValues)

If Not rfound Is Nothing Then
    RowNumber = rFound.Row
    Worksheets(wsEclec).Cells(RowNumber, 117).Value = TextBox10.Value
Else
    MsgBox "Search Value Not Found", vbInformation, "No Matching Text Found"
End If

End Sub

Upvotes: 1

Related Questions