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