Alok Paul
In Excel, we use both numeric data and string data. They may exist in the same column or the same cell. If we want to find cells with string data, we may do this manually. But the Excel VBA has some useful features by which we can identify those strings. We will discuss how to get a match string in a column using Excel VBA.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Examples of Excel VBA to Match String in Column
We will try to get match strings in Excel using the following dataset.
Now, let’s see the VBA examples one by one.
1. A Simple VBA Code to Get the Number of Matches
With this VBA code, we will get how many times the specified string is found in the data range. The point to be noted is that we will set the matching string within the VBA code and change this criterion within the code each time we want. Now, let’s explore the VBA code example step by step.
Step 1:
- First, go to the Sheet Name field at the bottom of the sheet.
- Press the right button of the mouse. Choose View Code from the list.
The VBA command module will appear. We will write VBA code in this module.
Step 2:
- Now, go to the dataset and select the range B5:D9.
- Copy and paste the following VBA code into this module.
Sub Match_String_1()
Dim count_1 As Integer
count_1 = 0
For i = 1 To Selection.Rows.Count
If Selection.Cells(i, 1) = "John" Then
count_1 = count_1 + 1
End If
Next i
MsgBox "Number of Matching found: " & count_1
End Sub
Step 3:
- Now, hit F5 to run the code.
We get 1 matching result from the 1st column of our selected range.
2. VBA Code with Range and Text Input to Count the Match
We will put the range and specific string directly on the code in this method.
Step 1:
- Press Alt+F11 to enter the command module.
- Write the following VBA code on the command module.
Sub Match_String_2()
Dim x As Long
Dim y, range_1 As Range
Set range_1 = Range("B5:B9")
With range_1
For Each y In .Rows
If Application.CountIf(y, "John") > 0 Then
x = x + 1
End If
Next
End With
MsgBox "Number of Matching found: " & x
End Sub
Step 2:
- Press F5 to run the VBA code.
We get the match number from this method.
3. Use VBA InputBox to Allow User to Input Reference String and Count the Match
This piece of VBA code will allow you to insert the reference string in an input box and count the number of matches each time you run the code.
Step 1:
- Hit Alt+F11 and enter the VBA command module.
- Copy the following VBA code and paste it into the module.
Sub Match_String_3()
Dim x As Long
Dim string_1 As String
Dim y, range_1 As Range
string_1 = InputBox("Enter Text")
Set range_1 = Application.InputBox("Please select a range", Type:=8)
With range_1
For Each y In .Rows
If Application.CountIf(y, string_1) > 0 Then
x = x + 1
End If
Next
End With
MsgBox "Number of Matching found: " & x
End Sub
Step 2:
- Press F5 to run the VBA code.
- A window will appear to put a string to find a match.
Step 3:
- Press OK.
- Another window will appear to select a range. Select the range from the dataset.
Step 4:
- Again, press OK on that window.
We get the matching amount like the previous two methods.
Similar Readings
- How to Vlookup and Pull the Last Match in Excel (4 Ways)
- Sum All Matches with VLOOKUP in Excel (3 Easy Ways)
- How to Match Names in Excel Where Spelling Differ (8 Methods)
4. Excel VBA to Get the Row Number Where the Matched String Is Located
We will find a match of a string in a column and get the row number.
Step 1:
- Press Alt+F11 to enter the command module.
- Put the following VBA code on the module.
Sub Match_String_4()
Dim match_quantity As Variant
Dim string_1 As String
string_1 = InputBox("Enter Text")
match_quantity = WorksheetFunction.Match(string_1, Range("B5:B9"), 0)
MsgBox ("The match was found in row " & match_quantity)
End Sub
Step 2:
- Press F5 to run the code.
- A new window will appear. Input a string on the marked box.
Step 3:
- Now, click OK to get the result.
We selected a range of B5:B9 and our match was found in the 5th row of that range.
Read More: How to Return Row Number of a Cell Match in Excel (7 Methods)
5. Use of VBA InStr Function to Match String
In the following examples, we will use the VBA InStr function to match strings from a reference.
5.1 Find a Match of String with the Position
We will get the position of the string that matches in a string stream.
- Enter the command module by pressing Alt+F11.
- Paste the following VBA code here and run the code by pressing the F5 key.
Sub Match_String_5()
MsgBox InStr("Welcome to Microsoft Excel", "Welcome")
End Sub
This function is case-sensitive. We modified the reference string of the VBA code. Now, run the code to see the result.
Sub Match_String_5()
MsgBox InStr("Welcome to Microsoft Excel", "excel")
End Sub
We are getting “0” in the result, as we did not find any match.
Read More: Excel Find Matching Values in Two Columns
5.2 Find Match of String with Position and Criteria
In this section, we will use criteria to get the position of a string.
- Hit Alt+F11 to enter the command module.
- Copy the VBA code and paste it into the module.
- Run the code by pressing the F5 key.
Sub Match_String_6()
MsgBox InStr(10, "What is Excel? Excel is a Microsoft Propety.", "Excel")
End Sub
We have “Excel” two times in the reference string stream. We skipped the 1st string by using a number that specified the position of the 1st string.
Read More: How to Find Case Sensitive Match in Excel ( 6 Formulas)
5.3 Find a Match of String from the Right Side and Get the Position
Usually, we count the position of a string from the left side. We can also count from the right side.
- Hit Alt+F11 and enter the VBA command module.
- Put the code below.
- Hit F5 to run the code.
Sub Match_String_7()
MsgBox InStrRev("What is Excel? Excel is a Microsoft Property.", "Excel")
End Sub
We counted the position of the string “Excel” from the right side.
Read More: How to Match Data in Excel from 2 Worksheets
5.4 Show If a Match of String Is Found or Not
We will try to show the matching status in the result.
- Enter the VBA command module by pressing Alt+F11.
- Write the following VBA code on the command module and run the code by pressing F5.
Sub Match_String_8()
If InStr("Welcome to Microsoft Excel", "Excel") = 0 Then
MsgBox "Match not found"
Else
MsgBox "Minimum one match exist"
End If
End Sub
5.5 Show Position If a Match of String Is Found or Not
This method will show the position of the string if a match is found otherwise, indicating that not been found.
- Enter the VBA command module by pressing the Alt+F11 keys.
- Copy and paste the below VBA Then, run the code by pressing the F5 button.
Sub Match_String_9()
Dim x As Long
x = InStr("Welcome to Microsoft Excel", "Excel")
If x = 0 Then
MsgBox "Word not found"
Else
MsgBox "Word found in position: " & x
End If
End Sub
5.6 Print the Match Status in a Newly Added Column
In this section, we will check the matching print status in the dataset.
- To enter the command module press Alt+F11.
- Now, write the VBA code on the module.
Sub Match_String_10()
Dim string_1 As Range
For Each string_1 In Range("C5:C9")
If InStr(string_1.Value, "Accounts") > 0 Then
string_1.Offset(0, 2).Value = "Yes"
Else
string_1.Offset(0, 2).Value = "No"
End If
Next string_1
End Sub
- Now, run the code by pressing F5.
We can see that if a match is found on the row then shows Yes, otherwise No.
Read More: Excel VBA: Copy Row If Cell Value Matches (2 Methods)
Conclusion
In this article, we described how to find a match string in a column using Excel VBA. We also showed how to get the match from a string stream. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
