How to automate search, display, print and archive data with Excel VBA. A user has inventory data like item code, quantity and price in Sheet1. In Excel Sheet2 he would like to input the item code in cell B3 and he wishes to display the item code, quantity and price in the range A6:C6 as soon as he clicks on the command button ‘search’. Once the data is displayed he would like to print the data in the form of a receipt for his customers. At the same time he would like to maintain a copy of the printed record in Sheet3 with the date.
Watch the training video below and then study the macro code step by step:
Let’s understand the VBA code for the search button:
On Error GoTo MyerrorHandler:
Dim itemcode As Long
itemcode = Sheet2.Range(“B3”)
Set myrange = Sheet1.Range(“A:C”)
If Range(“B3”) = “” Then
MsgBox “You didn’t enter any item code!”
Range(“A6”).Value = Range(“B3”).Value
Range(“B6”).Value = Application.WorksheetFunction.VLookup(itemcode, myrange, 2, False)
Range(“C6”).Value = Application.WorksheetFunction.VLookup(itemcode, myrange, 3, False)
If Err.Number = 1004 Then
MsgBox “Item code not in the data!”
If you notice we have used an error handling technique here. Why? Well, the user may enter an item code that does not exist in the Excel worksheet 1. Sometimes, he may click on the search button by mistake without entering an item code.
Now how do place the appropriate data in the relevant worksheet cells? The item code is copied by using a range reference. The quantity and price are got from the Sheet1 using the worksheet function Vlookup. As we already know, Vlookup looks for an item (itemcode) from a range of cells (myrange) in the columns to the right (column 2 and 3; column 1 has the item code) of the searched item (itemcode).
Macro code for the Print command button:
Dim erow As Long
Dim itemcode As Long
itemcode = Sheet2.Range(“b3”)
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
myselection = MsgBox(“Are you sure you want to print?”, vbOKCancel, “ALERT”)
Select Case myselection
Case vbOK ‘Yes, I want to proceed
Sheet3.Cells(erow, 1) = Date
Sheet3.Cells(erow, 2) = Sheet2.Range(“A6”)
Sheet3.Cells(erow, 3) = Sheet2.Range(“B6”)
Sheet3.Cells(erow, 4) = Sheet2.Range(“C6”)
Case vbCancel ‘ No!
We define a variable called ‘itemcode’ which gets its value from Range B3 in sheet2. Then using the Select Case and End Select, we perform a variety of actions based on our decision. If we decide to print the displayed data, then the macro code also transfers the date from the system, item-code, quantity and price to worksheet 3. In order to ensure that the next data does not over-write the earlier data, we first find the next blank row before transferring the next data automatically. In case we decide not to print the data due to some error then the VBA code ensures that this data is not transferred to sheet3. You will also notice that when the user clicks on the ‘print’ command button, he sees a print preview of the page. The ide is to save paper during the trials. If you wish to print out then a slight change achieves the goal.
VBA code for the ‘Clear’ command button:
The last line of code in the cleardata macro selects the range B3 so that you can start your work again quickly.
Download a sample file for practice: