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
In the first example, we will use the Find function in VBA to find a value in a column.
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. The VBA code will search Order ID using Product ID from 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
