Excel VBA to Find Value in Column (6 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Excel VBA to Find Value in Column: 6 Examples

1. Find Value in Column Using VBA Find Function

2. VBA to Find Value from Different Worksheets

3. Find and Mark Value in Column

4. VBA to Find Values in Column Using Wildcards

5. Excel VBA to Find Maximum Value in Column

6. Excel VBA to Find the Last Value in Column

Things to Remember

Conclusion

Excel VBA to Find Value in Column: 6 Examples

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.

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

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.

Similar Readings:

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.

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.

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.

Download Practice Workbook

Find Value in Column.xlsm

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.

Excel VBA to Find Value in Column (6 Suitable Examples)

Excel VBA to Find Value in Column: 6 Examples

1. Find Value in Column Using VBA Find Function

2. VBA to Find Value from Different Worksheets

3. Find and Mark Value in Column

4. VBA to Find Values in Column Using Wildcards

5. Excel VBA to Find Maximum Value in Column

6. Excel VBA to Find the Last Value in Column

Things to Remember

Conclusion

Further Readings

Leave a reply Cancel reply