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:
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:
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
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”)
Down a sample file by clicking on the Excel icon: