The sample dataset contains Product ID, Brand, Model, Unit Price, and Order ID. You want to find an Order ID associated with a Product ID.
Example 1 – Find a Value in a Column Using the VBA Find Function
Steps:
- Right-click the sheet name.
- Choose View Code.
- In Insert, select Module.
- Enter the following code.
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
- To insert a button in the dataset, go to the Developer tab.
- In Insert, choose Button (Form Control).
- Name the button. Here, Search.
- Assign the code to this button.
- Select it and right-click.
- Choose Assign Macro.
- Select the macro.
- Click OK.
- Enter any Product ID and click Search.
No match is showing, as the product number is not listed.
- Enter a new Product ID and click Search.
The order number for the given Product ID is displayed.
Read More: Excel VBA to Find Matching Value in Column
Example 2 – Using VBA to Find a Value in Different Worksheets
Product information is in Sheet 2, and the search box is in Sheet 3.
Sheet 2:
Sheet3:
Steps:
- Follow steps 1 and 2 in Example 1.
- Enter the following code.
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
- Insert a button.
- Assign the macro code to the button.
- Enter any Product ID and click Execute.
Read More: Excel VBA to Find Multiple Values in Range
Example 3 – Find and Mark a Value in a Column
The sample dataset has an additional column (Delivery Status). You want to mark Pending values.
Steps:
- Follow steps 1 and 2 in Example 1.
- Enter the following code.
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
- Go to the worksheet and run the code.
This is the output.
Read More: How to Find Exact Match Using VBA in Excel
Method 4 – Using VBA to Find Values in a Column Using Wildcards
In the dataset below, you want to find product prices using their Model.
Steps:
- Follow steps 1 and 2 in Example 1.
- Enter the following code.
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
- Insert a button.
- Assign the macro code to the button.
- Enter any partial Product ID and click Execute.
Example 5. Excel VBA to Find the Maximum Value in a Column
Steps:
- To find the maximum price:
- Enter the following VBA code in 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
- Press F5 to run the code.
- In Input, select the range in the dataset.
- Click OK.
The maximum value is displayed in the dialog box.
Example 6 – Using Excel VBA to Find the Last Value in a Column
You want to know the value of the last row or cell in a specific column.
Steps:
- Enter the VBA code below in 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
- Run the code by pressing F5.
The last value is displayed in the dialog box.
Read More: Find the Last Row with Data in a Range Using Excel VBA Macros
Things to Remember
Common Errors:
- Error: The FIND method can only find one value at a time.
- Error: #NA in VLOOKUP. If the searched value is not present in the given dataset, this function will return the #NA error.
- Range(“Cell_Number”).ClearContents is used to clear the previous value from the cell. Otherwise, it must be manually removed.
Download Practice Workbook
Further Readings
- FindNext Using VBA in Excel
- How to Find Blank Cells Using VBA in Excel
- Excel VBA: Find the Next Empty Cell in Range
