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

Excel VBA Find Value in Column

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.

Find Value in Column Using FIND Function in VBA

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

Excel VBA Find Value in Column

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

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

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

Excel VBA Find Value in Column

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

Now Insert a button

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

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

Step 6: Assign the code to this button

Assign the code to this button

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

Now write any Product ID and click on the Search button

Step 8: Check any Product ID and see the output

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.

Find Value from Different Worksheet in Excel VBA

Find Value from Different Worksheet in Excel VBA

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

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

Now again insert a button like the previous one

Step 4: Then assign the macro code to the button

Then assign the macro code to the button

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

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

Find and Mark Value in Column in Excel VBA

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

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

Now go to the worksheet and run the code

Step 4: See the output in the table

Excel VBA Find Value in Column

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.

Find Values in Column Using Wildcards in Excel VBA

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

VBA code for partial match using VLOOKUP function

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

Now again insert a button like the previous one

Step 4: Now assign the macro code to the button

Now assign the macro code to the button

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

Now enter any partial Product ID and press the button

Things to Remember

Common Errors When they show
One Value at a Time FIND method can find only one value at a time.
#NA in VLOOKUP If the searched value is not present in the given dataset, then this function will return this #NA error.
Clearing Previous Value Range(“Cell_Number”).ClearContents this portion is used to clear the previous value from the cell. Otherwise, the previous value needs to be removed manually.

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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo