In this article, I’ll show you how you can find a text within a range with VBA in Excel. You’ll learn to find the text using both exact and partial matches, also with both case-insensitive and case-sensitive matches.
Here we’ve got a data set with the Names, Authors, and Prices of some books of a bookshop called Martin Bookstore.
Our objective today is to find a specific text from the books using both exact and partial matches using Visual Basic Application (VBA).
1. Finding Exact Match Within a Range with VBA in Excel (Case-Insensitive Match)
First of all, we’ll develop a Macro to find out all the exact matches of a specific text within this range of data set using VBA for a case-insensitive match.
For example, let’s try to find out all the books of the author Charles Dickens.
You can use the following VBA code to execute this:
⧭ VBA Code:
Sub Exact_Match_Case_Insensitive()
Text = InputBox("Enter the Text: ")
Matching_Column = Int(InputBox("Enter the Column Number to Match the Text: "))
Returning_Column = Int(InputBox("Enter the Column Number to Return Values: "))
Output = ""
For i = 1 To Selection.Rows.Count
If LCase(Selection.Cells(i, Matching_Column)) = LCase(Text) Then
If i <> Selection.Rows.Count Then
Output = Output + Selection.Cells(i, Returning_Column) + vbNewLine + vbNewLine
Else
Output = Output + Selection.Cells(i, Returning_Column)
End If
End If
Next i
MsgBox Output
End Sub⧪ Note: This code produces a Macro called Exact_Match_Case_Insensitive.
⧭ Step by Step Procedure to Run the Code:
⧪ Step 1: Opening the VBA Window
➤ Press ALT+F11 on your keyboard. The VBA window will open.
⧪ Step 2: Inserting a New Module
➤ Go to the Insert tab in the VBA window.
➤ From the options available, select Module.
⧪ Step 3: Entering the VBA Code
➤ A new module window called “Module 1” will open.
➤ Insert the given VBA code in the opened Module.
⧪ Explanation of the Code:
- Sub Exact_Match_Case_Insensitive() starts a new Macro called Exact_Match_Case_Insensitive.
- Text = InputBox(“Enter the Text: “) asks you to enter the text to match that will be stored in the variable Text.
- Matching_Column = Int(InputBox(“Enter the Column Number to Match the Text: “)) asks you to enter the number of the column to match.
- Returning_Column = Int(InputBox(“Enter the Column Number to Return Values: “)) asks you to enter the number of the column to return values.
- Output = “” declares a new variable called Output whose initial value is set to be a blank string.
- For i = 1 To Selection.Rows.Count starts an iteration for each row of the selected range.
- If LCase(Selection.Cells(i, Matching_Column)) = LCase(Text) Then case-insensitively matches the value from each row and the specified column to the value Text inserted as input.
- If i <> Selection.Rows.Count Then decides whether the iteration has come to the last row or not.
- Output = Output + Selection.Cells(i, Returning_Column) + vbNewLine + vbNewLine accumulates the desired value in the variable Output with two New Line characters if the value matches and the row isn’t the last row.
- Output = Output + Selection.Cells(i, Returning_Column) accumulates the desired value in the variable Output with no New Line character if the value matches and the row is in the last row.
- End If ends the If block, Next i and Next j end the two for-loops.
- MsgBox Output shows the Output variable as the output.
- End Sub ends the Macro.
⧪ Step 4: Saving the Macro-Enabled Workbook
➤ Save the workbook as Excel Macro-Enabled Workbook.
⧪ Step 5: Selecting the Data Set
➤ Return to your worksheet.
➤ Select the range of cells from which you want to find the specific text. Here I’ve selected my data set B4:D13 (Without Column Headers).
The books to be found out (Books of Charles Dickens) are marked light brown for the sake of visualization.
⧪ Step 6: Running the Macro
➤ Then press ALT+F8 on your keyboard.
➤ A dialogue box called Macro will open. Select Exact_Match_Case_Insensitive (The name of the Macro) and click on Run.
⧪ Step 7: Entering the Inputs
➤ You will get three Input Boxes. The 1st box will ask you to enter the specific text that you want to match.
In this example, I want to get all the books of Charles Dickens. So I have entered Charles Dickens.
Note: This code works on the case-insensitive matches. So charles dickens, Charles dickens, CHARLES DICKENS, etc. will also work fine.
➤ The 2nd box will ask you to enter the number of the column where you want to match the text.
I want to match the text with the column called Author, which is located in 2nd in the data set. So I’ve entered 2.
➤ The 3rd box will ask you to enter the number of the column from which you want to return values when matches.
I want to return values from the column Book Name (The books of Charles Dickens). So I’ve entered 1.
⧪ Step 8: The Final Output!
➤ Then click OK.
➤ you will find a message box showing all the values that match your text (The books of Charles Dickens in this case).
Read more: How to Find Exact Match Using VBA in Excel
2. Searching for Exact Match Within a Range with Excel VBA (Case-Sensitive Match)
You can also develop a Macro to find out the values with case-sensitive exact matches.
Just change the 7th line of the code to this:
If Selection.Cells(i, Matching_Column) = Text Then
So the complete code now becomes:
⧭ VBA Code:
Sub Exact_Match_Case_Sensitive()
Text = InputBox("Enter the Text: ")
Matching_Column = Int(InputBox("Enter the Column Number to Match the Text: "))
Returning_Column = Int(InputBox("Enter the Column Number to Return Values: "))
Output = ""
For i = 1 To Selection.Rows.Count
If Selection.Cells(i, Matching_Column) = Text Then
If i <> Selection.Rows.Count Then
Output = Output + Selection.Cells(i, Returning_Column) + vbNewLine + vbNewLine
Else
Output = Output + Selection.Cells(i, Returning_Column)
End If
End If
Next i
MsgBox Output
End Sub
⧪ Note: This code produces a Macro called Exact_Match_Case_Sensitive.
⧪ Explanation of the Code:
The explanation is the same as the previous explanation, except the 7th line.
In the 7th line, If Selection.Cells(i, Matching_Column) = Text Then searches for a case-sensitive exact match.
⧭ Step by Step Procedure to Run the Code:
The steps to run the code are the same as the above method.
First, follow the initial steps, insert the code in a new module, save it as Excel Macro-Enabled Workbook.
Then come back to your worksheet, select the data set and run the Macro called Exact_Match_Case_Sensitive.
You will get the same 3 Input Boxes as before. In the first box, enter the specific text (Charles Dickens in this example. CHARLES DICKENS or charles dickens or anything else won’t do.)
In the 2nd box, enter the column number to match (2 in this example).
And in the 3rd box, enter the column number to return values (1 in this example).
Click OK and you will get all the books of Charles Dickens (Same as before).
Similar Readings:
- How to Find String with VBA in Excel (8 Examples)
- VBA Find in Column in Excel (7 Approaches)
- Find String in a Cell Using VBA in Excel (2 Methods)
- How to Find Substring Using VBA (9 Ways)
3. Finding Partial Match Within a Range with VBA in Excel (Case-Insensitive Match)
Up till now, we looked for only exact matches.
This time we will develop a Macro to look for partial matches.
We’ll go for case-insensitive matches first.
Let’s try to build a Macro that will return all the books that have the text “Ode” in them.
The VBA code will almost be the same as the previous two codes.
Just change the 7th line to this:
If InStr(LCase(Selection.Cells(i, Matching_Column)), LCase(Text)) Then
So the complete code now becomes:
⧭ VBA Code:
Sub Partial_Match_Case_Insensitive()
Text = InputBox("Enter the Text: ")
Matching_Column = Int(InputBox("Enter the Column Number to Match the Text: "))
Returning_Column = Int(InputBox("Enter the Column Number to Return Values: "))
Output = ""
For i = 1 To Selection.Rows.Count
If InStr(LCase(Selection.Cells(i, Matching_Column)), LCase(Text)) Then
If i <> Selection.Rows.Count Then
Output = Output + Selection.Cells(i, Returning_Column) + vbNewLine + vbNewLine
Else
Output = Output + Selection.Cells(i, Returning_Column)
End If
End If
Next i
MsgBox Output
End Sub
⧪ Note: This code produces a Macro called Partial_Match_Case_Insensitive.
⧪ Explanation of the Code:
The explanation is also the same as the previous explanation, except the 7th line.
In the 7th line, If InStr(LCase(Selection.Cells(i, Matching_Column)), LCase(Text)) Then searches for a case-insensitive partial match.
[See the InStr function of VBA in detail here.]⧭ Step by Step Procedure to Run the Code:
The steps to run the code are the same as the above two methods.
First, follow the initial steps, insert the code in a new module, save it as Excel Macro-Enabled Workbook.
Then come back to your worksheet, select the data set and run the Macro called Partial_Match_Case_Insensitive.
[The books to be found have been marked light brown for visualizing.]
Then you will get the same 3 Input Boxes as before. In the first box, enter the specific text (Ode in this example. ODE or ode or anything else will do, as this code works with case-insensitive matches.)
In the 2nd box, enter the column number to match (1 in this example, Book Name).
And in the 3rd box, enter the column number to return values ( Again 1 in this example).
Click OK and you will get all the books that contain the text “Ode” in them.
4. Searching for Partial Match Within a Range with Excel VBA (Case-Sensitive Match)
Now we will develop a Macro that will search for a partial match of a text within a range of texts, with a case-sensitive match.
The VBA code will almost be the same as the previous three codes.
Again change the 7th line to this:
If InStr(Selection.Cells(i, Matching_Column), Text) Then
So the complete code will become:
⧭ VBA Code:
Sub Partial_Match_Case_Sensitive()
Text = InputBox("Enter the Text: ")
Matching_Column = Int(InputBox("Enter the Column Number to Match the Text: "))
Returning_Column = Int(InputBox("Enter the Column Number to Return Values: "))
Output = ""
For i = 1 To Selection.Rows.Count
If InStr(Selection.Cells(i, Matching_Column), Text) Then
If i <> Selection.Rows.Count Then
Output = Output + Selection.Cells(i, Returning_Column) + vbNewLine + vbNewLine
Else
Output = Output + Selection.Cells(i, Returning_Column)
End If
End If
Next i
MsgBox Output
End Sub
⧪ Note: This code produces a Macro called Partial_Match_Case_Sensitive.
⧪ Explanation of the Code:
The explanation is also the same as the previous explanation, except the 7th line.
In the 7th line, If InStr(Selection.Cells(i, Matching_Column), Text) Then searches for a case-sensitive partial match.
[See the InStr function of VBA in detail here.]⧭ Step by Step Procedure to Run the Code:
The steps to run the code are the same as the above three methods.
First, follow the initial steps, insert the code in a new module, save it as Excel Macro-Enabled Workbook.
Then come back to your worksheet, select the data set and run the Macro called Partial_Match_Case_Sensitive.
[The books to be found have been marked light brown for visualizing.]
Then you will get the same 3 Input Boxes as before. In the first box, enter the specific text (Ode in this example. ODE or ode or anything else won’t, as this code works with case-sensitive matches.)
In the 2nd box, enter the column number to match (1 in this example, Book Name).
And in the 3rd box, enter the column number to return values ( Again 1 in this example).
Click OK and you will get all the books that contain the text “Ode” in them, not “ode” or “ODE”.
Download Practice Workbook
Conclusion
Using these methods, you can find any text value within a range with VBA in Excel, using both exact and partial matches. Do you have any questions? Feel free to ask us.
Related Readings
- How to Use the Find Function in VBA (6 Examples)
- VBA Find Last Row in Excel (5 ways)
- FindNext Using VBA in Excel (2 Examples)
- How to Find and Replace Using VBA (11 Ways)
