Excel VBA to Find Multiple Values in Range (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will discuss how to use Excel VBA to find multiple values in range. In a large dataset, it becomes difficult to find all the required values that the users want. So, they search for the values within the dataset. VBA codes offer users a quick and efficient way to go through the data and find all the values that they require.

In this video, we want to find multiple values in the main dataset based on the selected cells from the G column. The VBA code highlights the information related to those values from the dataset after running the code.


How to Launch VBA Macro Editor in Excel

In order to run any Excel VBA code, we first need to write or edit the code in the VBA Macro Editor.

Before that, we need to enable the Developer tab, which remains hidden by default. The Developer tab allows users to access the VBA Macro Editor. Do the following tasks.

  • Go to Developer Tab >> Visual Basic.
  • This will open the Visual Basic window.

Opening Visual Basic Window to find multiple values in range using Excel VBA

  • Select Insert >> Module in the macro editor.

Inserting VBA Module

  • As a result, an empty module will appear on the screen where you can write the code. However, use the Run icon or F5 key to run the code.

VBA Module


Excel VBA to Find Multiple Values in Range: 4 Ways

Today we are going to show 4 ways to find multiple values in a range with VBA in Excel. Here, we have a  dataset that contains the names of some famous works of literature, their authors’ names, genres, and publication years. The VBA codes in this article will prompt users to write or select the name or names of the author or authors that they want to find, and the codes will highlight them within the dataset.


1. Finding Multiple Values from User Input

Excel vba find multiple values in range Highlighting Values Relevant to User Input

In this method, code will prompt the users to insert a value that they want to find and highlight all the occurrences of that value within the dataset.

Code to Find Values from the User Input

Sub Find_from_UserInput()
Dim Rng As Range
Set Rng = Range("C5:C14")
user = InputBox("Please insert an Author's name")
For i = 1 To Rng.Rows.Count
    If LCase(Rng.Cells(i, 1)) = LCase(user) Then
        Range(Rng.Cells(i, 1).Offset(0, -1), Rng.Cells(i, 1).Offset(0, 2)).Interior.Color = vbGreen
    End If
Next i
End Sub

Code Breakdown:

Dim Rng As Range
Set Rng = Range("C5:C14")
user = InputBox("Please insert an Author's name")
  • Here, a Range type variable Rng is declared and assigned the C5:C14 range as its value.
  • The user variable will store the author’s name that the user will input in the InputBox.
For i = 1 To Rng.Rows.Count
    If LCase(Rng.Cells(i, 1)) = LCase(user) Then
        Range(Rng.Cells(i, 1).Offset(0, -1), Rng.Cells(i, 1).Offset(0, 2)).Interior.Color = vbGreen
    End If
Next i
  • In this section, a For Loop will run through the rows of the Rng range and match the values of each cell with the value of the user variable.
  • If the values match then the Range(Rng.Cells(i, 1).Offset(0, -1), Rng.Cells(i, 1).Offset(0, 2)).Interior.Color = vbGreen line will highlight the entire row of the matched value with green.

Follow the steps below to execute the method similar to the video,

  • Run the VBA Code.
  • Write the author’s name whose information you want to find.
  • The code will highlight the information in green.

Read More: Excel VBA to Find Matching Value in Column


2. Using Array to Find Multiple Values

Finding Values Matching Array Elements

In this instance, we will build an array containing the values to find. Then, the VBA code will highlight the values related to those values within the array.

VBA Code to Find Values Matching Array Elements

Sub Find_from_Array()
Dim Rng As Range
Dim Author() As Variant
Set Rng = Range("C5:C14")
Author = Array("George R.R.Martin", "Siddhartha Mukherjee")
For i = 0 To UBound(Author)
    For j = 1 To Rng.Rows.Count
        If Rng.Cells(j, 1) = Author(i) Then
        Range(Rng.Cells(j, 1).Offset(0, -1), Rng.Cells(j, 1).Offset(0, 2)).Interior.Color = vbGreen
        End If
    Next j
Next i
End Sub

Code Breakdown:

Dim Rng As Range
Dim Author() As Variant
Set Rng = Range("C5:C14")
Author = Array("George R.R.Martin", "Siddhartha Mukherjee")
  • Here, the code declares two variables Rng and Author.
  • Then, it assigns the C5:C14 range as the value of the Rng variable and builds an array using the Array function, with the elements of the array being the names of two authors.
For i = 0 To UBound(Author)
    For j = 1 To Rng.Rows.Count
        If Rng.Cells(j, 1) = Author(i) Then
        Range(Rng.Cells(j, 1).Offset(0, -1), Rng.Cells(j, 1).Offset(0, 2)).Interior.Color = vbGreen
        End If
    Next j
Next i
  • This portion has a For Loop within a For Loop.
  • The first For Loop iterates through the values of the Author array, and the next one loops through the rows of the Rng range.
  • Then it checks if the value in each row matches the current element of the Author array, and if so, it highlights a range of cells in that row with a green interior color.

Follow the steps below to execute the method,

  • Run the code by clicking on the green triangle in the VBA module.
  • The code will highlight the information of the authors present in the Author array (marked with a red rectangle).

Read More: Excel VBA to Find Value in Column


3. Finding Multiple Values from Selection

Finding Multiple Values from Selection

In this example, we will select some values from the Excel sheet, and the VBA code will highlight the information related to those values from the dataset.

VBA Code to Find Multiple Values from Selection

Sub Find_From_Selection()
'declaring variables
Dim Rng As Range
Dim Author() As Variant
Dim user As Range
Set Rng = Range("C5:C14")
'setting the value of the user variable to selected range in the worksheet
Set user = Selection
'counting row numbers of the selection
nRow = user.Rows.Count
'redimesionalizing the Author array
ReDim Author(1 To nRow)
'taking all the values of the user range into Author array
For i = 1 To nRow
    Author(i) = user.Cells(i, 1)
Next i
'running for loop to find match for array values and highligh them
For i = 1 To UBound(Author)
    For j = 1 To Rng.Rows.Count
        If Rng.Cells(j, 1) = Author(i) Then
            Range(Rng.Cells(j, 1).Offset(0, -1), Rng.Cells(j, 1).Offset(0, 2)).Interior.Color = vbGreen
        End If
    Next j
Next i
End Sub

Code Breakdown:

  • Here, we declare three variables, namely: Rng, Author, and user. Then, assign their values.  The value of the user variable is a range that is selected by the users before they run the code.
  • nRow = user.Rows.Count – this line counts the row number present in the user range. And finally, ReDim Author(1 To nRow) with this line, the code resizes the Author array which has an index from 1 to the number of rows the user range has.
For i = 1 To nRow
    Author(i) = user.Cells(i, 1)
Next i
  • This For Loop takes all the values of the selection range inside the Author array.
For i = 1 To UBound(Author)
    For j = 1 To Rng.Rows.Count
        If Rng.Cells(j, 1) = Author(i) Then
            Range(Rng.Cells(j, 1).Offset(0, -1), Rng.Cells(j, 1).Offset(0, 2)).Interior.Color = vbGreen
        End If
    Next j
Next i
  • This code loops over the elements of the Author array, and for each element, it loops over the rows in the Rng range.
  • For each row, it determines whether the value in the first column matches the current element of the Author array, and if it does, it highlights a range of cells in that row with a green interior color.

In the video, we followed the steps below to execute the task,

  • From the Find Author(s) column select the authors’ names whose information you want to find.
  • Run the VBA code.
  • As a result, the relevant data will be highlighted in green.

4. Using Range.Find Method to Find Multiple Values

In this case, we will use the Range.Find method to find multiple values with VBA in Excel. The Range.Find method can find values that match exactly or partially with the input value. All we need to do is change the MatchCase argument of the method.


4.1 Finding Exact Match

Finding Values with Exact Match

In this example, we will insert an author’s name into an InputBox and the code will highlight the information of the author whose name exactly matches the input.

VBA Code to Find multiple Values with Exact Match in a range in Excel

Sub RangeDotFindMethod1()
'declaring variables
Dim Rng As Range
Dim AuthorCell As Range
Dim Author As String
Dim FirstCell As String
'prompting users to input an author's name
Author = InputBox("Please Type an Author Name")
Set Rng = Range("C5:C14")
'using Find method to find the cell number where the input author first appeared
Set AuthorCell = Rng.Find(What:=Author, MatchCase:=True)
'error handling if the author's name is not on the list
If AuthorCell Is Nothing Then
    MsgBox "No author of such name is found"
Else
'running through the Rng range to find and highlight the author's information
    FirstCell = AuthorCell.Address
    Do
        Range(AuthorCell.Offset(0, -1), AuthorCell.Offset(0, 2)).Interior.Color = vbGreen
        Set AuthorCell = Rng.FindNext(AuthorCell)
    Loop While AuthorCell.Address <> FirstCell
End If
End Sub

Code Breakdown:

Here, the code declares 4 variables, namely: Rng, AuthorCell, Author, and FirstCell. Then, it asks the user to write an author’s name and store that inside the Author variable. It also sets the C4:C14 range as the value of the Rng variable.

Set AuthorCell = Rng.Find(What:=Author, MatchCase:=True)
  • This line searches the Rng range of cells for a cell that matches the Author value.
  • The Find method is used to perform the search, which returns the first cell that matches the search criteria.
  • The What argument specifies the value to search for, which in this case is the Author value.
  • The MatchCase argument is set to True, which means that the search is case-sensitive.
  • The result of the search is stored in the AuthorCell object variable.
If AuthorCell Is Nothing Then
    MsgBox "No author of such name is found"
Else
'running through the Rng range to find and highlight the author's information
    FirstCell = AuthorCell.Address
    Do
        Range(AuthorCell.Offset(0, -1), AuthorCell.Offset(0, 2)).Interior.Color = vbGreen
        Set AuthorCell = Rng.FindNext(AuthorCell)
    Loop While AuthorCell.Address <> FirstCell
End If
If AuthorCell Is Nothing Then
MsgBox "No author of such name is found"

If AuthorCell is empty or the code does not find any matching value with the Author value, then this line displays a message box to the user indicating that no matching author was found.

  • FirstCell = AuthorCell.Address: This line stores the address of the first match found by the Find method in the FirstCell variable.
  • Do: This starts a Do Loop that will run at least once and continue to run until the AuthorCell.Address value matches the FirstCell value (i.e. until all matching cells have been processed).
  • Range(AuthorCell.Offset(0, -1), AuthorCell.Offset(0, 2)).Interior.Color = vbGreen: This line selects a range of cells based on the current value of AuthorCell, using the same syntax as in the previous code examples. The interior color of this range is set to green using the vbGreen constant.
  • Set AuthorCell = Rng.FindNext(AuthorCell): This line uses the FindNext method to find the next cell that matches the search criteria (i.e., the same Author value). The result of this search is stored in the AuthorCell object variable.
  • Loop While AuthorCell.Address <> FirstCell: This line ends the Do loop, but only if the address of the AuthorCell value is not equal to the address of the FirstCell value (i.e., there are more matching cells to process). If the addresses match, then all matching cells have been processed, and the loop ends.

The ensuing steps are executed in the video above,

  • Execute the code from the VBA window.
  • The code will ask for the name of any author.
  • Enter the name.
  • All the values related to that author will be marked with green.

4.2 Finding Partial Match

Finding Values with Partial Match

Here, we will insert a proportion of authors’ names or any letter in an InputBox and the code will find all the values that match partially with that input.

VBA Code to Find multiple Values with Partial Match in a range in Excel

Sub RangeDotFindMethod2()
'declaring variables
Dim Rng As Range
Dim AuthorCell As Range
Dim Author As String
Dim FirstCell As String
'prompting users to input an author's name
Author = InputBox("Please Type an Author Name")
Set Rng = Range("C5:C14")
'using Find method to find the cell number in which the input first partially matches
Set AuthorCell = Rng.Find(What:=Author, MatchCase:=False)
'error handling if the author's name is not on the list
If AuthorCell Is Nothing Then
    MsgBox "No author of such name is found"
Else
'running through the Rng range to find and highlight the author's information
    FirstCell = AuthorCell.Address
    Do
        Range(AuthorCell.Offset(0, -1), AuthorCell.Offset(0, 2)).Interior.Color = vbGreen
        Set AuthorCell = Rng.FindNext(AuthorCell)
    Loop While AuthorCell.Address <> FirstCell
End If
End Sub
This code is the same as the code in the method of section 4.1. The only difference is that here in the Set AuthorCell = Rng.Find(What:=Author, MatchCase:=False) line the MatchCase argument is False which means the Find command will run a case-insensitive search. That is why the code will highlight all the values that match partially with the input.

Execute the method by applying the following steps as seen from the video,

  • Execute the VBA code from the VBA module.
  • Enter any letter or any portion of any author’s name in the InputBox.
  • The code will highlight all the partial matches.

How to Find and Replace Multiple Values with VBA in Excel

We often have to update certain values in a dataset. If the dataset is large, it becomes tedious to go through all the data to find the desired ones and replace them manually. You can easily find and replace any values using VBA code. The image below shows the values before finding and replacing them by running a VBA code.

Multiple values prevailing before running the Code and replacing them

 

The picture below shows the updated dataset after running the Code.

After running the Code the found values are replaced

So, here we have a VBA code that will automatically find the desired values and replace them with the values that we want.

Code to Find and Replace Multiple Values

 

Sub Find_and_Replace()
'declaring variables
Dim xrng As Range
Dim xInpRng As Range
Dim xRplsRng As Range
'title of the input prompt
Title = "Find and Replace Values"
'prompting users to select the range from where to find the values
Set xInpRng = Application.Selection
Set xInpRng = Application.InputBox("Find Values: ", Title, xInpRng.Address, Type:=8)
'asking user to select the replacement values
Set xRplsRng = Application.InputBox("Replace with: ", Title, Type:=8)
Application.ScreenUpdating = False
'finding values from the InputRng and replacing them with ReplaceRng values
For Each xrng In xRplsRng.Columns(1).Cells
    xInpRng.Replace What:=xrng.Value, Replacement:=xrng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub

Code Breakdown:

Here, we declare three Range type variables, namely: xrng, xInpRng, and xRplsRng.

Title = "Find and Replace Values"
Set xInpRng = Application.Selection
Set xInpRng = Application.InputBox("Find Values: ", Title, xInpRng.Address, Type:=8)
Set xRplsRng = Application.InputBox("Replace with: ", Title, Type:=8)
Application.ScreenUpdating = False
  • Title = “Find and Replace Values”: This line assigns a string value “Find and Replace Values” to the Title variable, which we used as the title of the InputBox.
  • Set xInpRng = Application.Selection: This line assigns the currently selected range of cells to the xInpRng object variable.
  • Set xInpRng = Application.InputBox(“Find Values: “, Title, xInpRng.Address, Type:=8): This line displays anInputBox to the user with the message “Find Values:”, using the Title variable as the title of theInputBox. The code displayed current address of the xInpRng range as the default value in the InputBox. We set the Type argument to 8, which specifies that the user can only select a range of cells as the input value. Then, we assigned selected range to the xInpRng object variable.
  • Set xRplsRng = Application.InputBox(“Replace with: “, Title, Type:=8): This line displays another InputBox to the user with the message “Replace with:”, using the Title variable as the title of the InputBox. Again, we set the Type argument to 8, which specifies that the user can only select a range of cells as the input value. The selected range is assigned to the xRplsRng object variable.
  • Application.ScreenUpdating = False: This line turns off screen updating to improve performance and reduce flicker on the screen.
For Each xrng In xRplsRng.Columns(1).Cells
    xInpRng.Replace What:=xrng.Value, Replacement:=xrng.Offset(0, 1).Value
Next
  • For Each xrng In xRplsRng.Columns(1).Cells: This line starts a loop that iterates over each cell in the first column of the xRplsRng range.
  • xInpRng.Replace What:=xrng.Value, Replacement:=xrng.Offset(0, 1).Value: This line replaces all occurrences of the value in the current cell (xrng.Value) in the xInpRng range with the value in the cell to the right of the current cell (xrng.Offset(0, 1).Value). We used the VBA Replace method to call on the xInpRng range, and set the What and Replacement arguments to the appropriate values for each iteration of the loop.

In the video above we followed the steps below,

  • Execute the code.
  • Select the range from where the code will find and replace the values.
  • After that select the values that you want to find as well as the values that will replace them.
  • The code will find the values and replace them accordingly.

Frequently Asked Questions

  • How do you reference multiple ranges in VBA?

One can reference multiple ranges in a VBA code by using commas in between the ranges. The following code can demonstrate that,

Sub MultiRanges()
Range(“B5:B10,E5:E12,G5:D10”).Interior.Color=vbGreen
End Sub

This code sets the back color of the cells of these ranges to green.

You can also name these ranges and use Named Ranges because as the number of ranges increases, the syntax Range(“B5:B10,E5:E12,G5:D10”) will get messy.

  • How do I filter multiple values in VBA?

In VBA, you can filter multiple values in a range using the AutoFilter method. You can specify multiple criteria for the filter by using an array of values.

Here’s an example of how to filter multiple values in VBA:

Sub MultiFilter()
    Dim MyRange As Range
    Set MyRange = Range("A1:D10")
     MyRange.AutoFilter Field:=1, Criteria1:=Array("Red", "Blue"), Operator:=xlFilterValues
End Sub

Here, we use the AutoFilter method to filter the range to show only values “Red” and “Blue” in column A. The Field argument specifies which column to filter (in this case, column A), and the Criteria1 argument specifies an array of values to filter by.


Download Practice Workbook

You can download the practice book here.


Conclusion

In this article, we have talked about 4 handy methods of using Excel VBA to find multiple values in range. These methods will allow users to find more than one value from a dataset just by running the codes and selecting or writing the required values. In a large dataset, these VBA codes will save a lot of time as well as help the users to escape the monotony of finding each value manually.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo