Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

vba

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.

like image 466
ashish vb Avatar asked Jul 28 '15 09:07

ashish vb


People also ask

How do you find a particular value in a column in Excel VBA?

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.

How do I find a word in Excel using VBA?

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.


1 Answers

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)
like image 178
Phylogenesis Avatar answered Oct 16 '22 18:10

Phylogenesis