Automatically Search for Excel Data, Display and Print Using VBA
HTML-код
- Опубликовано: 8 окт 2014
- How to search for an item in another worksheet by entering the item code in a different worksheet, displaying the data related to the item code like quantity and price and finally printing the retrieved data.
Also, if the data is not available in our list of items, we create a new worksheet with this data. This gives us a new list of items which are in demand.
We use Excel VBA to solve this problem quickly and easily.
Details with complete VBA code are available here:
www.exceltrainingvideos.com/au...
Get the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
If you are from India you can get this book here: amzn.to/2jzJGqU
I really thank you for your great videos on vba. Very easy to follow and I have become very proficient in it after watching them. Thanks a lot. You are such a great teacher!
Hello Dinesh, your video was very helpful for a project I was implementing for work.
Thank you for that.
Hello, I thank you very much for your efforts and your courses which allowed me to access the world of programming via VBA. Again Thank you very much for this considerable work.
Informative and well presented as usual Dinesh. Thank you!!
Thank you Mr Kumar, this information exactly what I was looking for, this is very helping my work
Well done. A real help. Clear presentation of the core ideas. Many thanks.
i like your videos so much, the method of your teaching is so simple and easy
Thank you! Very useful. Question: How do I create a log in sheet 2, in order to print a list of search results instead of just one item( Each time I search for an item, it should just be added to the list Eg. A12, B12, C12, then A13, B13, C13 etc.) and how do I use the enter button on the keyboard instead of creating a control button to search the items?
Very good demonstration and good example to print one line at a time.
Nice tutorial...
Thank you sir !
Very clear video instructions - thank you so much!!!
Which version of Microsoft Office has used in this tutorial?
is developer option is built in in your tutorial or it is installed another way?
Nice tutorial...
Thank you sir Dinesh Kumar Takyar.
hi
is it possible to write a program for taking particular data from the pdf file and paste in the particular cells of the excel sheet?
please help me with this
thank u
thanks Dinesh... your youtubes are great!!! have been very helpful for the projects that I am doing. Question... Can you please show me how to use vba to pull the last data entry of an closed workbook to use in my current workbook. many thanks
Mr Dinesh thank you so much for your tutorial..
hi sir i have some data set of companies i want to analyse them i need something that i put my data and automatically it calculates what i desire and print my desired graph as well is there any way through vba to do so
I am now subscribed! Thank you so much for this tutorial. It helped me complete my project 😊😊
More tutorial for dummies pls 😀
Thanks for subbing! Share the VBA tutorial with your friends too.
Hello Sir,
Thank you for this tutorial.
However I want little change in this method, I do not want the search tab and button but need the print button that print the last added data (ie. single row only) via userform.
Thanks in advance.
Thanks for the video. Really helpful. Just want to know, what if Item code is same for multiple rows. Eg: I have the same item code in two rows. Instead of quality let assume as colour. In my data i have paint name, colour and price. I have updated the sheet with the green and white colour for asasin paint availability. So if I gave Item as Asain Paints, then it should show the all the colours available in the asain pains. That is, it should search in the entire sheet and should get displayed that "Green" & "White" paints are available in asiain paints. can you help how to achieve this!
Hello Mr. Dinesh,
Thanks a lot for posting excellent videos of Excel VBA and teaching all the stuff from basic to High level.
Just want a favor from you - I have a good knowledge of VBA, but i want to go further more in this field. Can you please tell me know any course or certification programme is there for VBA so that i can also be a certified VBA programmer.
Thanks for the video. Is there a way to search every sheet. If I have a workbook with 40 sheets but want to search each one. Is there a way to generalize the 'sheet' search. Thanks
Thank you so much for this video!!!
Thank you sir !! worked fine ... had some issues with Sheet names and how i use it in the codes but was able to figure out
Thanks for this!! great job.
Hi, can u help me regarding on the userform, i want to print userform in correct layout? Everything is running fine on my VBA code except that when I'm printing the userform it is automatically saved as pdf file it is fine, but the problem is the layout, The layout format was set on the upper edge and left edge of the paper (it is not center) how to correct it?
Hello sir. Thank you very much for this, it has been a big help to much of our programming activities. I just wanted to ask about how I could make that a searchable drop-down list. Thank you very in advance.
hi sir,
good day!
Im trying to create a macro excel worksheet using oldest version or excel. we want to create something like a knowledge base where in when we search using a keyword all the possibke answer will show up... I hope you can help me... thanks so much in advance...
nice video sir, quick question how can i make my search result list all available instances and not just 1 instance? thanks
Dear Dinesh Kumar Takyar,
I have a probleme with the frame in userform VBA.
When i print an userform with the Frame, the title of Frame isn't printed.
so can you help me the probleme?
Thank you verry much
thanks a lot sir....really appreciate your effort .....nicely done....
Hello Sir. Thank you for videos function excel news .but could you lean to excel with file XML with VBA programming ?
Dear Sir, is it possible to get current printer name in excel cell / form text box though formula or VBA function. please help
Thank you Sir, Clearly explained in details and widly applicable.
Hi and Hello Sir what will be vba code if I want to populate combobox on the worksheet from another worksheet column
Sir any help will be greately appreciated
What shoud I do if I want to search multiple times and make a list of searched data to print out?
Thanks sir,
also want to create 1 button in userform which shows two more button then I have to select one button of them
Thank you Sir very helpful info.
I tried base on the code and it function. however, it only shows one results and no other data being show which also under the search condition. what should I edit to show all data when I click search?
Sir I need you help
Will you please guide me online through team viewer
Because I am having error during programing
Hi,
i've copied your exact code and changed the relevant areas i.e sheet names, range & cell etc, but my button doesn't seem to work? nothing happens, no error message nothing. Any idea why its not working. im not a regular user of excel vba so i dont know if im missing something?
If I had more than one of the same Item code and searched the code, how would i make it so it brings results for all records with that code?
Thank your veeery much, you gave me a link that has really helped me on my data management. but there is another thing. :)
How can i deduct a quantity from the total stock quantity, please note; the items are different with different stock quantities.
Thanks
i am ready to pay your remunaration for prepare my excel software. I need on search page such as 1,2,3..... from my previously prepared sheet 1. Please give reply.
Hi Sir
I need vba code for verify the number off sheets data cell by cell if there any errors(like all type of excel errors) of cell display first sheet name as error finding (include error name ,error adress, sheetname) similar to open files display on activeworkbook name as (errorfinding)....
Great Tutorials by Mr. Dinesh Kumar Takyar...............thanks
+vikas kumar You're welcome!
How do I print automatically when the value of a particular cell changes from 0 to 1 (Boolean value) ?
i don't know how to make that example like yours
i mean to remove the cells
or it's a template, something like that
thanks
Nice sir, but i just followed the instructions you outlined and i keep getting a feedback "lastrow = Sheets("item_price").Cells(Rows.count, 1).End(x1Up).Row" what will i do?
Thank you Dinesh,
Mavis' payments are with other customers in the same database and when i did only one of Mavis' payments appears.
lastrow = Sheets("Database").Cells(Rows.count, 4).End(xlUp).Row
Rem Dinesh
Rem This is it
For x = 5 To lastrow
If Sheets("Database").Cells(x, 5) = cboSearch.Value Then
Sheet4.Range("A3") = Sheets("Database").Cells(x, 4)
Sheet4.Range("B3") = Sheets("Database").Cells(x, 5)
Sheet4.Range("C3") = Sheets("Database").Cells(x, 7)
Sheet4.Range("D3") = Sheets("Database").Cells(x, 8)
Sheet4.Range("E3") = Sheets("Database").Cells(x, 13)
Rem I have made it such that it displays both on a different sheet and on a userform
Sheet4.Range("A4") = Sheets("Database").Cells(x, 4)
Sheet4.Range("B4") = Sheets("Database").Cells(x, 5)
Sheet4.Range("C4") = Sheets("Database").Cells(x, 7)
Sheet4.Range("D4") = Sheets("Database").Cells(x, 8)
Sheet4.Range("E4") = Sheets("Database").Cells(x, 13)
Thanks for your response
Many many thanks to you for prepare the my required excel software by your guidance. can i upload the software for your verifiction?
how to search MORE data.in this video only one item search but i am need more items search plz reply me
I HOPE YOU
Very nice video!
Thanks alot
Very nice and useful lesson. But I have a question for you. Am a document controller and I deal with the latest revision, when I applied it in my database it will search for any revision how then can it search for the latest revision?
Excellent video 👏👏👍
sir how to copy paste that data into another sheet instead of printing ?? please help
Sir, I need your help. I am struggling with this. I will give you Scenario
Everything is around 3 sheets named List, Template and Journal
Sheet no 1. Name "List" following tables there.
Date, Name, Department, Item, Approval Amount, Actual Spend, Supplier, Budget Code, Backup ... Including two buttons Template and Journal
Sheet no.2 has some fixed data with some empty cells. Only 2 rows are in use A6: I6 and A7:I7and Sheet no. 3 is empty.
What i want is when user click on Template Button only specific data of the last row from sheet no 1 "LIST" should copy into Empty cells in Template sheet.( for example if user click on Template. Data from sheet 1 from table " ITEM, ACTUALl SPEND and BUDGET CODE copy to Template Sheet like ITEM data copy to cell H6 and H7 , SPEND data Copy to E6 and E7 and Budget Code copy to B7. So the Template button function is to show data from last or current or most recent data from Sheet LIST.
where if user click on Journal It shows exactly same data generate in template ( Including fixed data in template) for all available dates.
I can send you the excel sheet for more detail. I would aprciate if you help me with this. Thanks
regards, Umer
sir how to save it sir? i really want to know, after i followed your videos it works but i dont know how to save my work, please teach me sir,,, thank you.
Thanks a lot sir ji you are great
hi anybody help how to search record from protected excel sheet
Hii Sir
Many thanks for your tutorial. but can you explain me how to write the logic if my requirements are as follows.
Conditions: if one cell contains "abc" or "klr" or "gfe" (can be both or any combination or single) then show Mumbai in right most cell.
So i have multiple conditions like this to check in every cells.
this logic should run for all the filled cells in a column.
i have solved this by using nested if & innumber functions. but as i have more than 50 conditions are there i want to make it with macro.
can you pls give me a example for 3-4 conditions in macro so that i can write it for 50 conditions.
hello i need some help i follow this videos and i did the same stuff but if i click on search i or type the item code nufin come is like my search command is not working and can i have a direct mail where to mail you sir please, thank you for this videos
Thanks Dinesh sir u r simple & informative
Please share with your friends also. Thanks.
what I have is the same with your formula for x = 2 To lastrow
Sir. Agar last row ko aik sa zayada bar type kr na ho to to kasa ho ga. Same sheet Mai. B's ka sariel number change ho. Baki data same hi rha. Plz explain me in userform mahi. Jasa aik itam ki quanutty ki 5 quity like ha to maa chahta ka wo ka us ko 5 rows Mai likha aor in ka sariel number change ho bs
ill try this one.You've been a big help Dinesh thanks for the videos!
How can I modify this code to read from an excel data table rather than cells?
i am ready to pay your recrimination for prepare my excel software. I need on search page such as 1,2,3..... from my previously prepared sheet 1. Please give reply.
Run-time error '438':
Object dosn't support this property or method
whats the vba for the search button?
hello sir. i want to search by customer name for customer all details,like customer name,mobile number,Address, due amount,ETC, if i search by mobile number it's details data are showing. Sir i need your help how to search by name !! how to write code for this ?. thank you Sir .
The data I use, for one ID number exists more than one search result . How to display them all in another sheet ?
Hi, how can we do this using excel 2013?
sir thanks for u very use full to me
thank you sir
sir i want the range a11:c11 go down for every search like a11:c30
Sir, Thanks a lot for this information. Just a small doubt if we have multiple entry for the same Search word and we wanted to get output all these multiple entry then how to do here.
Have a look at this link: www.exceltrainingvideos.com/tag/findnext-method-vba/
Or do a search at www.exceltrainingvideos.com
if we have multiple entry for the same Search word and we wanted to get output all these multiple entry doesn't work
santosh bhagat did u get it? I too want the code where it displays all items with the same item no.
Sir, i want this button on advance user form please help.
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
+J Lu This link might solve the problem:www.exceltrainingvideos.com/extract-data-from-one-excel-worksheet-to-another-using-advanced-filter-with-vba/
I like watch and learn this as you explain properly. It’s really helpful for retail inventory, but i have on question on my mind that , If i want to hide and protect the page than how to do that ? Also how to update the sheet as we receive new barcode in a few days .
Your reply will be highly appreciated.
Search my channel including the playlists and my website www.exceltrainingvideos.com for the solutions.
Thank you so much for your excellent description of how to do this. I was able to implement it flawlessly.
I do however have a question. Is there a way for excel to search a file directory for a file and have it display the extension to the file or even the file object itself so that you can click on it an open it (for example a .pdf document)? Your help would be greatly appreciated.
sir i need u r help
please help i got error runtime error 438 object doesnt support this property or method , please help
Thanks for sharing.
Hi Dinesh thank for this video, but I have a query. Can you help me how to extract if ITEM code is multiple entry. I wanted to know how search for multiple ITEM CODE
This link will help: www.exceltrainingvideos.com/tag/find-extract-multiple-values-using-find-findnext-vba/
Hello Dinesh Sir, your video tutorials are extremely helpful. i wanted to know is there any way through which i can define current week or a week later without using loop? e.g. i want macro to filter for Week 41 (current week) and another filter for previous week 40. how can define that in macro so that automatically put a filter for current week and 1 week later?
Thanks in advance.
??
I want to create this form for extracting client specific information. for example I have 500 companies for each companies there will be product mgr , technical mgr , team lead . with address n ol but I want to extract only these 3 peoples names please help me to create this.
why its not working in mac excel 2016??
i keep getting a feedback "lastrow = Sheets("item_price").Cells(Rows.count, 1).End(x1Up).Row" what should i do?
Dear Sir thanks for the video , i am planning to make an excel sheet for petty cash voucher and i want summary of all the vouchers created in same workbook in but in different sheets and want to control the serial number with summary in another sheet in shame workbook could you please advice?
Search step by step www.exceltrainingvideos.com and you should be able to find a solution quickly.
sir,
i have two rows have the same name, what I try to do is to put that name in the search sheet and get both rows information
Dear Sir,
When i used the formula in my sheet i got error in "lastrow = Sheets(“Sheet1”).Cells(Rows.count, 1).End(xlUp).Rows" . it show Subscription out of range. and i made same of your demo sheet, i got still same result.
please advise , how it solve
Before throwing a question check your code carefully. www.exceltrainingvideos.com/automatically-search-excel-data-display-print-using-vba/
Sir lets suppose on this sheet two row contains two different colour with data is this possible to add something on this code to get that row reflected with colour as output by using same search engine
You'll have to adopt the 'copy-pastespecial' strategy. PasteSpecial can help keep the original format. This link will guide:
www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
Or search www.exceltrainingvideos.com
Thank you Mr. D k Takyar, I tried it and it work very very fine, but I still have some little problem. I created it with order form and it only show the first cell instead of all. instance I have 5 cells
1
2
3
4
5
it only the first line not all 5, how can I code it to show all 5 cells please? Thanks
Please elaborate.
Thank you sir, for nice video on this subject, can any please tell me if I have to search matching data from multiple sheets, what to do ?. If you have posted such video, please tell me or post a new video.
You'll have to use a looping process.
By any chance can i save this file as (shared) will the command still functions for multple user??
You can save the macro enabled file on Onedrive and share it with multiple users quickly and easily. This 'old' VBA tutorial will help: www.exceltrainingvideos.com/how-to-share-excel-files-on-the-net/
Just a small doubt if we have multiple entry for the same Search word and we wanted to get output all these multiple entry then how to do here.
Have a look at this video: ruclips.net/video/8S4EdPJevlA/видео.html
It's a good idea to search my channel or website www.exceltrainingvideos.com
Do you have a video regarding Search, Display, Edit and Save to existing content?
Search www.exceltrainingvideos.com
can't find the code I'm looking for on that site
Thank you for everything you do by showing all this trick. I do have a question
How can i create a similar search form that will display the data and then in turn allow me to add a comment to the search result and save it using Vba ? Thank you once again
Example: Sheet2.Range(“D11”) = "My Comment"
Sir i want to search a data on basis of date..but i am unable to find that data...please help me
Very nice and usefull. Great
Glad you liked it!
Dinesh Sir, Shall I upload my data to rectify some errors to search data code? if you agree how can i upload the sheet? Sir please help me once.
takyardinesh@gmail.com If time permits.