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.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
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 info@exceltip.com
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.
Hi,
these two procedures are great.
I would like to use them in my work, but in the procedure for searching data I need a solution that in the event of not finding the data returns any information about it, and does not leave the result of the previous search.
Regards
Michael
Hai All. I have successfully retrieved Information I wanted using the code from this blog. I made required changes though. It works fine now. However it works only on one field and retrieves only one row. I want to retrieve multiple rows using this search method. That is, I have a table with 4 columns
Code Company Location Remarks
for example , I want to retrieve rows with "Location" value as "India"
I want the code to retrieve all the rows with Location - India to be displayed
How can I achieve this change. Please help.
Thank You again.
Pramod
Hai, A few doubts.
1. Here Sheets ("Data") is the sheet that contains all the data correct? You have named the sheet as Data.
2. You have mentioned "In the next sheet (sheet2)..." but in code it is sheet3
I have copied the same code for my table but throws an exception Runtime Error 424 Object required at the first line of code itself....
If Sheets("Data").Cells(X, 1) = Sheet3.Range("B3") Then
I checked, rechecked, all sheet names, field names column names all are exactly same as per code but what is the object that is missing , I am not able to figure out.
I have only 4 columns Name, Age, Gender and Mobile
I have removed some part of the code from line 4 since I do not have those columns in my table. Only difference in my table are table headings.
Please help me
Actually, here we have used sheet name (sheets("Data").range) and sheet code name (sheet2.range) both. Both are valid. Sheet code name can be seen in the project explorer of VBA. it is like Sheet1("data"). Here Sheet1 is the code name and the name in the parenthesis and quotes.
for simplicity, If your sheet's name is Sheet2 (excel view) then use the below line.
Sheets("Data").Cells(X, 1) = Sheets("Sheet2").Range("B3") ThenHey Manish
Thank you for the reply.
Yesterday I was breaking my head as to what happened it does not work when I renamed Sheet2 to something else. I couldnt see it. Today with a fresh mind I started on it and found that out 🙂
In the following code,
If Sheets("Industries").Cells(X, 2) = sheet2.Range("B3") Then
It wont error if the sheet name is sheet2. I guess excel takes the default value it gives without specifying name. But when I renamed it to Report, it stopped working.
If Sheets("Industries").Cells(X, 2) = Report.Range("B3") Then
I changed sheet2 to Report in code but it was not working, then I understood the issue and corrected it to the following and it worked
If Sheets("Industries").Cells(X, 2) = Sheets("Report").Range("B3") Then
So once you rename the sheet you have to explicitly tell excel the sheet name as Sheets(" sheetname").
Thank you.
hi exceltips,
thanks for your suggestion please could you tell me in my file micros and modules not saving when i am going to open
iam receiving like this message
"cannot run the macro "Inventory", the macro may not be available in this workbook or all macros may be disabled".
can you help me
Regards,
Sam
Hi Sam,
Are you saving the file as .xlsm (macro-enabled workbook)? If you save your file as .xlsx, the macro's won't get saved.
How can I get all the result if there is more than one row in the search result? I mean for example there is 2 person under one Agent id, so in 1001 agent id has tow name and address so it should give me 2 lines of output now it is making just one.
Please help
I would also like to know this. I'm trying to return multiple data entries for each City that I have in my sheet, so when you type in the city name it shows the data associated with that City.
Hi Jennifer and Choudhury,
In that case, it would batter to use filters instead of find. Filter.
https://www.exceltip.com/excel-macros-and-vba/vba-code-to-filter-data.html
Thank you! I was able to get the filter to work perfectly, and now I'm trying to get the filtered data only to read back to the input sheet (sheet3 in your search example). The current code used with the search reads the last row of data, but I would like it to read and return from the last filtered row to the first row-all to be shown on the input sheet. Any ideas?
subscript out of range errror is faced here can you plz help me?
Lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row
Check the sheet name. most probably it is because of the sheet name. Check the sheet name. It must be named as "data" or else change the sheet name here. If still causes issues, share the whole code.
Hey, Thank you for the code. Im trying it but getting run time error 424. any ideaS?
Sub Searchdata()
Dim Lastrow As Long
Dim count As Integer
Lastrow = Sheets("Students").Cells(Rows.count, 1).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("Students").Cells(i, 2) = Till.Range("A6:B6") Then
Till.Range("B8") = Sheets("Students").Cells(i, 2)
'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 i
End Sub
i am searching so many days this type of excel sheet for manage the attendance. but i am so lucky that you have provide this type of excel sheet. but problem is that your searching engine search only one item. but i required many item search from sheet 1 to search page. If possible please help me.
Thanking you