April 21, 2018

Search Multiple Values in Worksheet Data

How to search multiple values in worksheet data using VBA. Earlier we had learnt how to search for data in a worksheet using a looping process but were able to find a single value. Today we learn to extract all the occurrences of data in our worksheet like an item code with VBA. Watch the video below:

Watch this video on YouTube.

Here’s the complete VBA code to find multiple data withe the same value:

Sub searchMultipleValues()
Dim erow As Long
Dim ws As Worksheet
Dim lastrow As Long
Dim count As Integer

lastrow = Sheets(“item_price”).Cells(Rows.count, 1).End(xlUp).Row
Sheet2.Range(“a11:C1000”).ClearContents

count = 0

Dim p As Long

p = 11

For x = 2 To lastrow

If Sheets(“item_price”).Cells(x, 1) = Sheet2.Range(“b3”) Then
Sheet2.Cells(p, 1) = Sheets(“item_price”).Cells(x, 1)
Sheet2.Cells(p, 2) = Sheets(“item_price”).Cells(x, 2)
Sheet2.Cells(p, 3) = Sheets(“item_price”).Cells(x, 3)
p = p + 1
count = count + 1
End If
Next x

MsgBox ” The number of data found for this item code is ” & ” ” & count

If count = 0 Then
Set ws = Worksheets(“Sheet3”)
With ws
erow = .Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
.Cells(erow, 1) = Date
.Cells(erow, 2) = Sheet2.Range(“B3”)
End With
End If

Sheet2.Range(“A10”).Select

End Sub

 

10 thoughts on “Search Multiple Values in Worksheet Data

  1. Hio sir
    if i have a Userform instead of sheet2 how i will get the data from sheet to userform. textbox CboReg is the search textbox
    check
    RD OFFSET NSL
    200 -14.5 100.25
    200 -10.5 100.12
    200 0.000 100.0
    200 2.650 100.325
    200 5.000 100.600

  2. Hello, i have learned so much from you. Thank you. Can i ask is there any way i could edit a row of data data from the multiple search results and save it back to the main sheet?
    Maybe by selecting the row i wish to edit?

  3. Good Day Sir,
    I was excite with your video, I am new to Excel VBA and the video on multiple search data is something I can really use. After several attempts to write the code and copy and paste. I did not have any luck.

    Is there any opportunity to send a file with the codes and I can just edit the workbook.

  4. Do you have a download of the workbook with code?
    I have tried to recreate this in Excel 2016 and i continue to get run time errors etc it will not work at all as you show.

    can you share the workbook with all the code?

    Thank you

  5. From the VBA code to find multiple data withe the same value I was able to successfully do the same for my requirement, also could you please help me with how to sort the date in descending when the data is displayed

  6. Sir, you are one of my best mentor. I have tried this tutorial, its working fine. I observed here the search keyword to be exact match.
    Could you please help me to modify this VBA code where search keyword will be partial name string of product name/details instead of product number.

    Example: for an excel data table of Computer accessories ; if search keyword is “mo” then Motherboard, Mouse, Mouse Pad all these will display. If search keyword is “Mouse” or “mouse” then it will appear only Mouse, Mouse Pad.

    Thank you

Comments are closed.