Md. Abdullah Al Murad
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 value in a column in Excel VBA.
Download Practice Workbook
6 Examples of VBA to Find Value in Column in Excel
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.
1. Find Value in Column Using VBA Find Function
In the first example, we will use the Find function in VBA to find a value in a column.
📌 Steps:
- Go to the sheet name at the bottom of the sheet.
- Press the right button of the mouse.
- Choose the View Code option from the list.
- The VBA window opens. Then select Module from the Insert option
- Now write the following code in the VBA console
Sub Find_Value()
Dim ProductID As Range
Range("D4").ClearContents
Set ProductID = Range("B8:B19").Find(What:=Range("C4").Value, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not ProductID Is Nothing Then
Range("E5").Value = ProductID.Offset(, 4).Value
Else
MsgBox "No Matched Data Found !!"
End If
End Sub
- Now Insert a button in the dataset.
- Go to the Developer tab.
- Choose Button (Form Control) from the Insert section.
- Give any name of the button. Like I am giving it as Search.
- Assign the code to this button.
- Select the button and press the right button of the mouse.
- Choose Assign Macro from the list.
- Select the desired macro from the Assign Macro window.
- Then, press OK.
- Now write any Product ID and click on the Search button.
We can see No match is showing, as this product number is not on the list.
- Put another Product ID and again press the Search button.
We get the order number for the given Product ID.
Read More: How to Get Cell Value by Row and Column in Excel VBA
2. VBA to Find Value from Different Worksheets
Now in this section, we will do the same thing above but for different worksheets. Let’s assume that our product information is in Sheet 2, and the search box is in Sheet 3. Now we will write the VBA code so that we can search for Order ID using Product ID from Sheet 3.
Sheet 2:
Sheet3:
📌 Steps:
- Follow the same steps from step 1 to step 2 from the previous method to open the VBA console
- Now write the following code in the VBA console
Sub Find_Value_from_worksheets()
Dim rng As Range
Dim ProductID As String
Dim rownumber As Long
ProductID = Sheet3.Cells(4, 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(5, 5).Value = Sheet2.Cells(rownumber, 6).Value
End Sub
- Now again insert a button like the previous one.
- Then assign the macro code to the button.
- Enter any Product ID and click on the Execute button
Read More: Lookup Value in Column and Return Value of Another Column in Excel
3. Find and Mark Value in Column
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.
📌 Steps:
- Follow the same step from step 1 to step 2 as the previous method to open the VBA console
- Now write the following code in the VBA console
Sub Find_and_Mark()
Dim strAddress_First As String
Dim rngFind_Value As Range
Dim rngSrch As Range
Dim rng_Find As Range
Set rng_Find = ActiveSheet.Range("G1:G16")
Set rngSrch = rng_Find.Cells(rng_Find.Cells.Count)
Set rngFind_Value = rng_Find.Find("Pending", rngSrch, xlValues)
If Not rngFind_Value Is Nothing Then
strAddress_First = rngFind_Value.Address
rngFind_Value.Font.Color = vbRed
Do
Set rngFind_Value = rng_Find.FindNext(rngFind_Value)
rngFind_Value.Font.Color = vbRed
Loop Until rngFind_Value.Address = strAddress_First
End If
End Sub
- Now go to the worksheet and run the code.
- See the output in the table.
Read More: How to Find Highest Value in Excel Column (4 Methods)
4. VBA to Find Values in Column Using Wildcards
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.
📌 Steps:
- Follow the same step from step 1 to step 2 as the previous method to open the VBA console
- Now write the following code in the VBA console
Sub Find_Using_Wildcards()
Set myerange = Range("B5:G16")
Set ID = Range("D19")
Set Price = Range("F20")
Price.Value = Application.WorksheetFunction.VLookup _
("*" & Range("D19") & "*", myerange, 4, False)
End Sub
- Again, insert a button like the previous one.
- Now assign the macro code to the button.
- Now enter any partial Product ID and press the Execute button.
Read More: How to Find Lowest Value in an Excel Column (6 Ways)
5. Excel VBA to Find Maximum Value in Column
Here, we want to find the maximum value of a column using the VBA code.
📌 Steps:
- We want to find out the maximum price.
- Now, put the following VBA code on a new module.
Sub Column_Max()
Dim range_1 As Range
Set range_1 = Application.InputBox(prompt:="Select Range", Type:=8)
Max_Column = WorksheetFunction.Max(range_1)
MsgBox Max_Column
End Sub
- Then, press the F5 button to run the VBA code.
- The Input dialog box appears.
- Select the range from the dataset.
- Finally, press the OK button.
We can see the maximum value is shown in the dialog box.
6. Excel VBA to Find the Last Value in Column
Here, we want to know the value of the last row or cell of a specific column. For example, we want to know the last product from the Product column
📌 Steps:
- Input the VBA code below on the module.
Sub last_value()
Dim L_Row As Long
L_Row = Cells(Rows.Count, "B").End(xlUp).Row
MsgBox Cells(L_Row, 2).Value
End Sub
- Then, run the code by pressing the F5 button.
The last value is shown in the dialog box.
Read More: How to Find Last Occurrence of a Value in a Column in Excel (5 Methods)
Things to Remember
Some Common Errors:
- Error: One Value at a Time. Because the FIND method can find only one value at a time.
- Error: #NA in VLOOKUP. If the searched value is not present in the given dataset, then this function will return this #NA error.
- Range(“Cell_Number”).ClearContents 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. Visit our website ExcelDemy for more interesting articles on Excel.
