How to Find a text within a Range Using VBA in Excel - 4 Methods

The sample dataset contains Book Names, Authors, and Prices.

1. Finding an Exact Match Within a Range with VBA in Excel (Case-Insensitive Match)

Find all books by Charles Dickens.

This is the 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 creates the Macro: Exact_Match_Case_Insensitive.

Step 1: Opening the VBA Window

Press ALT+F11.

Step 2: Inserting a New Module

Go to the Insert tab and select Module.

Step 3: Entering the VBA Code

Enter the code in “Module 1”.

Code Breakdown

Sub Exact_Match_Case_Insensitive() starts a new Macro: 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: Output whose initial value is set to a blank string.
For i = 1 To Selection.Rows.Count starts an iteration for each row in the selected range.
If LCase(Selection.Cells(i, Matching_Column)) = LCase(Text) Then case-insensitively matches the value in each row and specified column to the value Text inserted as input.
If i <> Selection.Rows.Count Then decides whether the iteration reached the last row
Output = Output + Selection.Cells(i, Returning_Column) + vbNewLine + vbNewLine accumulates the 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 if the value matches and the row is 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 Dataset

Go back to the worksheet.
Select the range of cells to find the specific text. Here, B4:D13 (Without Column Headers).

Books by Charles Dickens are marked light brown.

Step 6: Running the Macro

ALT+F8 on your keyboard.
In the Macro dialog box, select Exact_Match_Case_Insensitive (The name of the Macro) and click Run.

Step 7: Entering the Input Values

3 Input Boxes. are displayed.

In the 1st box, enter the specific text you want to match. Here, Charles Dickens.

Note: This code works with case-insensitive matches. So charles dickens, Charles dickens, CHARLES DICKENS, etc. will also work.

In the 2nd box, enter the number of the column to match the text. Here 2.

In the 3rd box, enter the number of the column from which to return matched values. Here, 1.

Step 8: The Final Output

Click OK.

A message box will display all books by Charles Dickens.

Read more: How to Find Exact Match Using VBA in Excel

Method 2 – Searching for Exact Match Within a Range with Excel VBA (Case-Sensitive Match)

You only need to change the 7th line of the code used in Method 1:

If Selection.Cells(i, Matching_Column) = Text Then

The complete code is:

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 creates a Macro: Exact_Match_Case_Sensitive.

Code Breakdown

As described in Method 1, except for the 7th line:

If Selection.Cells(i, Matching_Column) = Text Then searches for a case-sensitive exact match.

Run the Code:

Follow the steps described in Method 1.
Enter the code in a new module and save it as an Excel Macro-Enabled Workbook.
Go back to the worksheet, select the dataset and run the Macro: Exact_Match_Case_Sensitive.

3 Input Boxes are displayed.

In the first box, enter the specific text . Here, Charles Dickens.

In the 2nd box, enter the column number to match. Here, 2.

In the 3rd box, enter the column number to return values. Here, 1.

Click OK.

You will see all books by Charles Dickens.

Similar Readings:

Method 3 – Finding a Partial Match Within a Range with VBA in Excel (Case-Insensitive Match)

To find all books with the text “Ode”:

Change the 7th line in the code:

If InStr(LCase(Selection.Cells(i, Matching_Column)), LCase(Text)) Then

The code is:

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 creates the Macro: Partial_Match_Case_Insensitive.

Code Breakdown

As described in Method 1, except for 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.]

Run the Code:

Follow the steps described in Method 1.
Enter the code in a new module and save it as an Excel Macro-Enabled Workbook.
Go back to the worksheet, select the dataset and run the Macro: Partial_Match_Case_Insensitive.

[The books to be found are in light brown.]

3 Input Boxes are displayed.

In the first box, enter the specific text . Here, Ode.

In the 2nd box, enter the column number to match. Here, 1.

In the 3rd box, enter the column number to return values. Here, 1.

Click OK.

You will see books containing the text “Ode”.

Method 4 – Searching for a Partial Match Within a Range with Excel VBA (Case-Sensitive Match)

Change the 7th line in the code:

If InStr(Selection.Cells(i, Matching_Column), Text) Then

The code is:

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 creates a Macro: Partial_Match_Case_Sensitive.

Code Breakdown

As described in Method 1, except for 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.]

Run the Code:

Follow the steps described in Method 1.
Enter the code in a new module and save it as an Excel Macro-Enabled Workbook.
Go back to the worksheet, select the dataset and run the Macro: Partial_Match_Case_Sensitive.