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.
To find a cell with a numeric value in a column, set the SearchDirection parameter to either of the following, as applicable: xlNext (SearchDirection:=xlNext): To search for the next match. xlPrevious (SearchDirection:=xlPrevious): To search for the previous match.
FIND or popular shortcut key Ctrl + F will find the word or content you are searching for in the entire worksheet as well as in the entire workbook. When you say find means you are finding in cells or ranges isn't it? Yes, the correct find method is part of the cells or ranges in excel as well as in VBA.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With