In this article, we are going to learn how to create a user form for search and print through VBA in Microsoft Excel.
Let’s understand with a simple exercise:-
We have data in Sheet 1 in which column A contains Agent Id, column B contains Name, Column C Address, column D city, column E region, column F country and column G contains postal zip.
In the next sheet (sheet 2), we want to retrieve the details according to the below-mentioned image:-
If we search the Agent Id, then all details should appear in the range A11:D11. And then give the printout.
Follow below given steps to Search and Print the details:-
Sub Searchdata() Dim Lastrow As Long Dim count As Integer Lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row For X = 2 To Lastrow If Sheets("Data").Cells(X, 1) = Sheet3.Range("B3") Then Sheet3.Range("A11") = Sheets("Data").Cells(X, 1) Sheet3.Range("B11") = Sheets("Data").Cells(X, 2) Sheet3.Range("C11") = Sheets("Data").Cells(X, 3) & " " & Sheets("data").Cells(X, 4) _ & " " & Sheets("data").Cells(X, 5) & " " & Sheets("Data").Cells(X, 6) Sheet3.Range("D11") = Sheets("Data").Cells(X, 7) End If Next X End Sub
Code Explanation:- First, we will define the variables and then we have defined which row will be the last row, and then we have defined what and where the Agent Id will be searched and then how the range will be updated for the searched Agent ID.
Now, we will assign this macro to the Search button.
Run the Macro:-
To give the print out, assign the below-mentioned macro:-
Sub PrintOut() Sheet3.Range("A1:D12").PrintPreview Sheet3.Range("A1:D12").PrintOut End Sub
In this way, we can search the data and then can give the printout to the searched details through VBA in Microsoft Excel.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.