CODEWITHSUNDEEP
vbaexcel
ashish vb
ashish vb

Reputation: 133

How to find the exact text string in a column of sheet using excel vba code

How can I find the exact string matching to particular cell value using Excel VBA. For example if I want to search "userid"(whole string only) in column A. I am able to write some lines of code but the program is not able to find the whole word, even if I type some of the letters in the string matching the whole word. Code is as given below:

Private Sub CommandButton1_Click()
Dim Username As String, password As String
Dim FindRow1 As Range, FindRow2 As Range
Dim WB As Workbook

Username = "user"
password = "pass"
Set WB = ThisWorkbook

With WB.Sheets("Master_Data")
Set FindRow1 = .Range("A:A").Find(What:=Username, LookIn:=xlValues)
Set FindRow2 = .Range("B:B").Find(What:=password, LookIn:=xlValues)
End With
MsgBox FindRow1
MsgBox FindRow2

Here I am getting output in msgbox as userid and password even if I pass the values as username = "user" and password = "pass" which is logically wrong.

Upvotes: 13

Views: 63324

Answers (1)

Phylogenesis
Phylogenesis

Reputation: 7890

Use the LookAt parameter of Range.Find():

Set FindRow1 = .Range("A:A").Find(What:=username, LookIn:=xlvalues, LookAt:=xlWhole)
Set FindRow2 = .Range("B:B").Find(What:=password, LookIn:=xlvalues, LookAt:=xlWhole)

Upvotes: 27

Related Questions