How to Find Value in Column Using VBA in Excel (4 Ways)

In MS Excel we frequently need to search or find values in the dataset. It could be finding data in the rows or the column. Fortunately, Excel provides different functions and formulas to do these kinds of tasks. With the help of Excel VBA code, we can automate this searching or finding values task. In this article, we will see different ways to find values in a column in Excel VBA.

Download the Practice Workbook

4 Ways to Find Value in Column in Excel VBA

1. Find Value in Column Using FIND Function in VBA

Let’s have a dataset of product information with their Product ID, Brand, Model, Unit Price, and Order ID. Our task is to find out the matched order ID. Now our task is to find out the Order ID associated with the Product ID.

Step 1:  Select Visual Basic under the Developer tab (Shortcut Alt + F11)

Step 2: A VBA window will open. Then select Module from the Insert option

Step 3: Now write the following code in the VBA console

Code:

Sub Find_Value()
 Dim ProductID As Range
 Range("D4").ClearContents
 Set ProductID = Range("B8:B19").Find(what:=Range("B3").Value, LookIn:=xlValues, lookat:=xlWhole)
 If Not ProductID Is Nothing Then
  Range("D4").Value = ProductID.Offset(, 4).Value
 Else
 MsgBox "No Matched Data Found !!"
 End If
End Sub

Step 4: Now Insert a button

Step 5: Give any name of the button. Like I am giving it as Search

Step 6: Assign the code to this button

Step 7: Now write any Product ID and click on the Search button

Step 8: Check any Product ID and see the output

2. Find Value from Different Worksheet in Excel VBA

Now in this section, we will do the same thing above but for different worksheets. Let’s assume that our product information is in Sheet2, and the search box is in Sheet3. Now we will write the VBA code so that we can search Order ID using Product ID from Sheet3.

Step 1: Follow the same steps from step 1 to step 2 from the previous method to open the VBA console

Step 2: Now write the following code in the VBA console

Code:

Sub Find_Value_from_worksheets()
 
 Dim rng As Range
 Dim ProductID As String
 Dim rownumber As Long
 
 ProductID = Sheet3.Cells(3, 3)

 Set rng = Sheet2.Columns("B:B").Find(What:=ProductID, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    rownumber = rng.Row
    Sheet3.Cells(4, 5).Value = Sheet2.Cells(rownumber, 6).Value
 
End Sub

Step 3: Now again insert a button like the previous one

Step 4: Then assign the macro code to the button

Step 5: Enter any product ID and click on the button

3. Find and Mark Value in Column in Excel VBA

Let’s see how we can find out values from a column by marking them. For this, let’s assume the same dataset above with an additional column named Delivery Status. Now our task is to mark the values in the Delivery Status column which are Pending

Step 1: Follow the same step from step 1 to step 2 like the previous method to open the VBA console

Step 2: Now write the following code in the VBA console

Code:

Sub Find_and_Mark()
 Dim strFirstAddress As String
 Dim rngFindValue As Range
 Dim rngSearch As Range
 Dim rngFind As Range
 Set rngFind = ActiveSheet.Range("G1:G15")
 Set rngSearch = rngFind.Cells(rngFind.Cells.Count)
 Set rngFindValue = rngFind.Find("Pending", rngSearch, xlValues)
 If Not rngFindValue Is Nothing Then
   strFirstAddress = rngFindValue.Address
   rngFindValue.Font.Color = vbRed
   Do
     Set rngFindValue = rngFind.FindNext(rngFindValue)
     rngFindValue.Font.Color = vbRed
  Loop Until rngFindValue.Address = strFirstAddress
 End If
End Sub

Step 3: Now go to the worksheet and run the code

Step 4: See the output in the table

4. Find Values in Column Using Wildcards in Excel VBA

Lastly, we will see how we can search or find values in columns using wildcard characters in Excel VBA. Again, we will use the same dataset above for this method. Our task is to find out product prices using their Model. We could either type the full name or last/first characters of the Product ID.

Step 1: Follow the same step from step 1 to step 2 as the previous method to open the VBA console

Step 2: Now write the following code in the VBA console

Code:

Sub Find_Using_Wildcards()
 Set myerange = Range("B4:G15")
 Set ID = Range("D18")
 Set Price = Range("F19")
 Price.Value = Application.WorksheetFunction.VLookup("*" & Range("D18") & "*", myerange, 4, False)
End Sub

Step 3: Now again insert a button like the previous one

Step 4: Now assign the macro code to the button

Step 5: Now enter any partial Product ID and press the button

Things to Remember

Conclusion

These are some ways to find values in columns using VBA code in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.

Further Readings

• Lookup Value in Column and Return Value of Another Column in Excel