Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.

Find Value in Column Using FIND Function in VBA


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

A VBA window will open. 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

Excel VBA Find Value in Column

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

Then assign the macro code to the button

  • Enter any Product ID and click on the Execute button

Enter any product ID and click on the 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.

Find and Mark Value in Column in Excel VBA

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

Excel VBA Find Value in Column

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.

Find Values in Column Using Wildcards in Excel VBA

📌 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 assign the macro code to the button

  • Now enter any partial Product ID and press the Execute button.

Now enter any partial Product ID and press the 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

Find maximum value of column using Excel VBA

  • Then, press the F5 button to run the VBA code.
  • The Input dialog box appears.
  • Select the range from the dataset.

Select range to find maximum value of column

  • 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

VBA to find maximum value in column

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


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo