Find within a Range with VBA in Excel: Including Exact and Partial Matches

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.


Download Practice Workbook


4 Easy Methods to Find a Value within a Range with VBA in Excel

Here we’ve got a data set with the Names, Authors, and Prices of some books of a bookshop called Martin Bookstore.

Data Set to Find a Value within a Range with VBA in Excel

Our objective today is to find a specific text from the books using both exact and partial matches using Visual Basic Application (VBA).


1. Find 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 data set using 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.

Opening VBA Window to Find a Value within a Range with VBA in Excel

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.

VBA Code to Find a Value within a Range with VBA in Excel

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.

Saving Workbook to Find a Value within a Range with VBA in Excel

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.

Running Macro to Find within Range with VBA in Excel

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.

Entering Inputs to Find a Value within a Range with VBA in Excel

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.

Entering Inputs to Find a Value within a Range with VBA in Excel

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).

Output to to Find a Value within a Range with VBA in Excel

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


2. Search Exact Match Within a Range with VBA in Excel (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.

Running Macro to Find a Value within a Range with VBA in Excel

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.)

Entering Inputs to Find a Value within a Range with VBA in Excel

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).

Entering Inputs to Find a Value within a Range with VBA in Excel

Click OK and you will get all the books of Charles Dickens (Same as before).

Output to to Find a Value within a Range with VBA in Excel


Similar Readings:


3. Find 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

VBA Code to Find a Value within a Range with VBA in Excel

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.)

Entering Inputs to Find a Value within a Range with VBA in Excel

In the 2nd box, enter the column number to match (1 in this example, Book Name).

Entering Inputs to Find a Value within a Range with VBA in Excel

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.

Output to Find a Value within a Range with VBA in Excel


4. Search for Partial Match Within a Range with VBA in Excel (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

VBA Code to Find a Value within a Range with VBA in Excel

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.)

Entering Inputs to Find a Value within a Range with VBA in Excel

In the 2nd box, enter the column number to match (1 in this example, Book Name).

Entering Inputs to Find a Value within a Range with VBA in Excel

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”.

Output to Find a Value within a Range with VBA in Excel


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

Tags:

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo