Automatically Search Excel Data Display Print Using VBA

How to automatically search for Excel data, display and print Using VBA: Let’s say we have a huge list of items. A customer wants to know the price and the quantity of the item available. Or, patients visit a hospital. They have an appointment with a specific doctor at a specific date and time. Now you wish to give every patient who visits the hospital a confirmation chit. Or, you wish to display the report of a student and quickly print it. We can solve such problems quickly and easily in Excel using VBA.
First we create a form right on an Excel worksheet using the Excel worksheet cells. Let’s say we have a label in cell A3 called ‘Item Code’. In cell B3 we enter an item code. Now we compare this item code with the data in another Excel worksheet. If we find the item code we can display the item code, quantity and price in cells A11, B11 and C11. We also have relevant headers in cells A10, B10 and C10. If we don’t find a matching item code, we can transfer the item code and the date of query into another worksheet to analyze this data at an appropriate time. We can also use a button form control to print the extracted data if required.
This how the Excel worksheet form looks:

automate-data-extraction-display-print-using-excel-vba

automate-data-extraction-display-print-using-excel-vba

Watch the video below to learn how we can automate the complete process of comparison of data, displaying the relevant connected data and finally printing a report:

Watch the video on YouTube.

The complete VBA code to automate the above process:
Sub searchdata()
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
count = 0
For x = 2 To lastrow

If Sheets(“item_price”).Cells(x, 1) = Sheet2.Range(“B3”) Then
Sheet2.Range(“A11”) = Sheets(“item_price”).Cells(x, 1)
Sheet2.Range(“B11”) = Sheets(“item_price”).Cells(x, 2)
Sheet2.Range(“C11”) = Sheets(“item_price”).Cells(x, 3)
count = count + 1
End If

Next x
If count = 0 Then
Set ws = Worksheets(“sheet3”)
erow = ws.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(erow, 1) = Date
ws.Cells(erow, 2) = Sheet2.Range(“B3”)
Sheet2.Range(“A11:C11”).ClearContents
End If

End Sub

Sub printdata()
Sheet2.Range(“A10:C12”).PrintPreview
‘Sheet2.Range(“A10:C12”).PrintOut

End Sub

Further reading:

Excel VBA

Excel VBA Books on Amazon India

Down a sample file by clicking on the Excel icon:

27 thoughts on “Automatically Search Excel Data Display Print Using VBA

  1. Dave

    Thank you for this wonderful code to Automatically Search Excel Data Display Print Using VBA.
    It would be nice to add the ability to search thru several worksheets of data in the same workbook using the user form search entry term. Thank you!

    Reply
    1. shzj

      By the command Invoice.[A1:M44].PrintOut it goes to the default printer, If we need to select the printer using the vba code then what is the vba code for it and then the rest of the code should get execute.

      Reply
  2. dayanidhi

    Dear sir,

    when i try to run this program, it show me error message as

    ” variable not defined ”
    hightlights on x

    can you please help me with this

    Reply
  3. Burney Meyer

    Hi dear how to or what to do to get multiple result from one info, because the item sheet I make are numbers and those number repeat but by difrent days and I want to see the results of the day that they repeat, and how to search for resulds in difrent culums

    Reply
  4. Henry Sun

    Thank you for this wonderful code to Automatically Search, but if there are multiples record, then this VBA code is not able to display multiple record, so please kindly advise.

    Reply
  5. Toufik

    Hello,

    thank you for this wonderful code , i have the same question as (Burney Meyer) , how to display the multiple result.

    thanks for you help
    regards,

    Reply
  6. ibrahim

    Sub searchdata()

    Dim erow As Long
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim count As Integer

    lastrow = Sheets(“staf”).Cells(Rows.count, 1).End(xlUp).Row

    For x = 3 To lastrow

    If Sheets(“staf”).Cells(x, 2) = Sheet2.Range(“E3”) Then

    Sheet2.Range(“B8”) = Sheets(“staf”).Cells(x, 2)
    Sheet2.Range(“C8”) = Sheets(“staf”).Cells(x, 3)
    Sheet2.Range(“D8”) = Sheets(“staf”).Cells(x, 4)
    Sheet2.Range(“E8”) = Sheets(“staf”).Cells(x, 5)
    Sheet2.Range(“F8”) = Sheets(“staf”).Cells(x, 6)
    Sheet2.Range(“G8”) = Sheets(“staf”).Cells(x, 7)
    Sheet2.Range(“H8”) = Sheets(“staf”).Cells(x, 8)
    Sheet2.Range(“I8”) = Sheets(“staf”).Cells(x, 8)
    Sheet2.Range(“J8”) = Sheets(“staf”).Cells(x, 9)
    Sheet2.Range(“K8”) = Sheets(“staf”).Cells(x, 10)
    Sheet2.Range(“L8”) = Sheets(“staf”).Cells(x, 11)
    Sheet2.Range(“M8”) = Sheets(“staf”).Cells(x, 12)
    Sheet2.Range(“N8”) = Sheets(“staf”).Cells(x, 13)
    Sheet2.Range(“O8”) = Sheets(“staf”).Cells(x, 14)
    Sheet2.Range(“P8”) = Sheets(“staf”).Cells(x, 15)
    Sheet2.Range(“Q8”) = Sheets(“staf”).Cells(x, 16)

    End If

    Next x

    End Sub

    can someone help me?? this the code that i want to run. but when i click on search.. the data wont display

    Reply
  7. jeff

    good stuff..thanks for sharing. got a question how to have it display if in data sheet it has the same item number but two different quantities ? i would like to have display for quantities.

    thanks again

    Reply
  8. Yamy

    Loved it!
    Quick question: What if, when searching again (Sheet2), instead of replacing the previous result, the new search result, would show in a new row? How can we do that?

    Reply
  9. Dale

    Hi,

    I have tried this code however I’m coming up with a 424 error. Can you please help. Thank you. The code I’m using is:

    Sub searchdata()
    Dim erow As Long
    Dim ws As Workbook
    Dim lastrow As Long
    Dim count As Integer

    lastrow = Sheets(“Sheet1”).Cells(Rows.count, 1).End(xlUp).Row

    For x = 7 To lastrow

    If Sheets(“Sheet1”).Cells(x, 1) = Dashboard.Range(“D6”) Then – -> I get the 424 pointing here
    Dashboard.Range(“C11”) = Sheets(“Sheet1”).Cells(x, 2)
    Dashboard.Range(“D11”) = Sheets(“Sheet1”).Cells(x, 13)
    Dashboard.Range(“E11”) = Sheets(“Sheet1”).Cells(x, 12)
    End If

    Next x
    End Sub

    Reply
  10. robin

    Hi,
    Thanks for such a useful video. I have a question here if my data stored in another workbook, how can i do it to search and copy data without open the workbook. You got another video on this but this search and copy function is more useful for me.

    Would appreciate your help.

    Thanks

    Reply
  11. CATHERINE CABREROS

    Hi,

    I wish to ask the template of your video
    Using Invoice Template to Automate Invoice Creation and Printing with VBA.
    So I would be able to easily know where are my errors on the file I made

    Reply
  12. daniel

    Hi anyone could help me? I tried testing it exactly the same at show in this utube but error 9 at eh below code. I am using office 2013. thanks
    lastrow = Sheets(“item_price”).Cells(Rows.count, 1).End(xlUp).Row

    Reply
  13. Tariqjahangeer

    Dear sir,
    I am trying to make data search display but there are some error are displaying could you please provide me following work sheet which one you created will you mind kindly mail me thanks

    Reply
  14. Kandha

    Here, I’m able to export only one line(Row) item which is matching from price list to sheet2. If I type i7cpu I’m getting only one line of data with the same row. Let’s tack I7cpu has diffrent number of quantity and price so, I want all the data to be displayed when clicking which is relevant to i7cpu. Example I7cpu has diffrent number of quantity and price . If now I’m going to type I7cpu I need all diffrent quantity and price of I7cpu. Please kindly advice me a code or give me a mail thanks.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *