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!
Dear Dinesh Kumar Takyar Greetings, First of all thank you very much for this video i just have a question What if there is 2 or 3 rows have the same item code and you want the search result showing all rows? Thanks
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
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
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?
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.
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.
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.
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
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
These links will help: th-cam.com/video/8S4EdPJevlA/w-d-xo.html www.exceltrainingvideos.com/search-multiple-values-in-worksheet-data/ Searching my channel and website would have given you more satisfaction!
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?
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.
Thank you very much for the video Sir, it is very helpful and clear. Is there anyway, we can search multiple ITEM numbers instead of one. I really appreciate you help. Again, Thank you
Thanks Dinesh. Question, why don't you use ws as a variable instead of spelling out the worksheet all the time? Dim ws as worksheet: set ws = this workbook.worksheets("item_price") Then you can just refer to it ad ws Ws.cells(x,1)
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
Hi, this video was really helpful for my work. I have however a question, I also want to populate A12, B13,C14 with other Item code without loosing the information already on A11,B11,C11.How could I do that, can you help? Many thanks,
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.
Thanx alot for useful information, but i have one question, if i have 4 items with the same name and i search for one, there should have next search option..
Good Morning Dinesh Sir, Myself i am working as if an accounts manager, i had tried your formula in vba it was really helpful , but i would like to ask you that if had same item name three times repeated with different rate and i want to display it one behind the other what should i do? i kindly request you to assist me with the code to apply as i am not good in VBA. Regards Shiju. Once again Thanks for the knowledge you are giving to many peoples , may god bless you.....
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
hello sir.. i think this one is suitable for our tiny district Emergency Department For Patient Registration.. but the problem is how to make a half A4 paper size for patient case note that can printable
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
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 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.
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
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.
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
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?
Is it possible to directly search data in the cell to google by using formulas. If yes can you please guide me how to do it. Or any other options to do it?
Dinesh Kumar Takyar I have put the company name (Apple) in cell A1 and a social media name (Facebook) in B1 and i have got the result as ( Apple Facebook) in cell C1. Now i want to search this cell C1 in google but i want to do it with formula and not manually, as i am searching for many social media channels at one time. Can you please guide me on this? or any other similar thing.
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.
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!
Thanks for the video. I have a question on this , what if i want to use the item price file in the same manner but it is stored on sharepoint. Please help
@@Exceltrainingvideos i need to store the item price file on sharepoint , so that if the tool is in multiple PCs , we only update the sharepoint file and it can be used by everyone or is there any alternative to sharepoint for this.
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
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?
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...
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?
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
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.
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.
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
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
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
Loop through all the worksheets in the workbook as shown in this link: www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/ Alternatively search exceltrainingvideos.com
Hello Sir thank you very much for replying I love learning excel, and you teach very well, What I am trying to archive is search for a part number say ABC123456-A01 this will then display the gauges needed for this part number, because the gauges cam be plug gauges with a tolerance from 3.750 - 3.3850 or a thread gauge M2,5 x 45 even core gages etc, I could type in the part number and will display all the gauges for this job, I altered you brilliant example to sort of work,
Hi Dinesh Kumar Takyar, I like your videos and have tried to use this one however keep getting a 424 error and cannot for the life of me workout what the issue is. Are you able to help if I post the code? Thanks Dale
+Dinesh Kumar Takyar - I am relatively new to this and have checked my code however couldn't find the error. I've posted below for your review and feedback. Thank you for such a quick reply. Sub searchdata() Dim erow As Long Dim ws As Workbook Dim lastrow As Long Dim count As Integer lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row For x = 7 To lastrow If Sheets("Data").Cells(x, 1) = Dashboard.Range("D6") Then 'This is where I'm getting the 424 error' Dashboard.Range("C11") = Sheets("Data").Cells(x, 2) Dashboard.Range("D11") = Sheets("Data").Cells(x, 13) Dashboard.Range("E11") = Sheets("Data").Cells(x, 12) End If Next x End Sub
+Dale Newman Try this code: Sub searchdata() Dim erow As Long Dim ws As Workbook Dim lastrow As Long Dim count As Integer lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row For x = 7 To lastrow MsgBox "The value is " & Sheets("Dashboard").Range("D6") If Sheets("Data").Cells(x, 1) = Sheets("Dashboard").Range("D6") Then 'This is where I'm getting the 424 error' Sheets("Dashboard").Range("C11") = Sheets("Data").Cells(x, 2) Sheets("Dashboard").Range("D11") = Sheets("Data").Cells(x, 13) Sheets("Dashboard").Range("E11") = Sheets("Data").Cells(x, 12) End If Next x End Sub
+Dinesh Kumar Takyar - I tried your code and when I clicked on search a msgbox popped up and showed me the value in D6 and i could not close the msgbox. I had to control alt delete to stop it.
+Dale Newman - I took the Msgbox out and when I entered a value in D6 I didn't get the 424 error however no results showed in C11, D11 or E11. Just so I'm clear x, 2 x, 13, x, 12 are the columns I'm pulling data from?
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
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/
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?
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
Hello Dinesh, Thank you for your very helpful video! I keep encountering an error when I try to run the code in Excel 2010. I am getting a "runtime error 9 subscript out of range" message and when I open the Visual Basic debug it highlights the "lastrow" statement. Kindly help as this tool will be every beneficial to a project I am working on.... Thanks Again
+Dinesh Kumar Takyar thank you for your reply, they are correct - I am stumped. Is it possible to kindly share your actual excel 2010 file from the video and then I can modify it to suit my project? I can provide my email address
+oscarpennstate Hi Dinesh, Please disregard my previous comment - However I have an additional question. Is there a way to search for only segments of a text string instead of an exact text string? For example if I have multiple item codes with the numbers "808" in the item code, can the search button return multiple results that have "808" in the item code?
Now what if i have the same serial number with different quantities on different lines because they are in different locations, Is there a way i can have it search through the entire work sheet and have it give me the sum total of quantities? Thanks in advance.
Dinesh Kumar Takyar I just ended up using a =sumif function to find the total values from multiple rows. Thanks again. I think i was just trying to put too much thought into it.... took me a little bit before i realized i could just use the sumif function to get me my numbers. Thank You!
Thank you for another great video. Have question for you sir, I have rows of data in sheet1 (lets say in column A, A1 is header). How can i calculate Average, Min and Max values in Sheet2 (lets say A2=average, A3=Min and A4=Max). There is no fixed numbers of rows on Sheet1, it could be data from A2:A300 or A2:A400. Please help as I have been trying this for a while.
Dinesh Kumar Takyar Thanks for the quick reply Dinesh! Is it possible to use VBA macro for this? I have added a button, when clicked it should calculate average, min, max values from Sheet1 - store values on variables and then paste on Sheet2 (assigned cells, example A2, B2, C2). I am also thinking of using End(xlUp) to find the last row for better calculating values. My first row is a header, so i dont want to include first row in the calculations. Please guide me on this task. Thanks!
Dinesh Kumar Takyar This is what I have so far: Private Sub CommandButton2_Click() Dim flow_a as Single Dim mydata As Workbook With Worksheets("Sheet1").Select flow_a = Range("C2:C302") End With With Worksheets("Sheet2").Select Cells(7, 8).Value = Application.WorksheetFunction.Average(flow_a) End With End Sub I am getting "run-time error: Type mismatch" on line "flow_a = Range("C2:C302").
You can use 'AND' for multiple true criteria and 'OR' for any true criteria. This link will guide: www.exceltrainingvideos.com/automate-date-validation-using-vba/ Or search www.exceltrainingvideos.com
Hello, regards from Mexico, This code is so useful for what I'm trying to do, and I'm forcing myself to resolve the next issue because I just don't seem to understand why this wouldn't work, my sheets are called "Data"(where the list comes from) and "Capture" (where I want it to be printed), here's the code as I modified it, I hope you can help me this is driving me insane Sub searchdata() Dim lastrow As Long lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row count = 0 For x = 2 To lastrow If Sheets(“Data”).Cells(x, 1) = Capture.Range(“W14”) Then Capture.Range(“W21”) = Sheets(“Data”).Cells(x, 1) Capture.Range(“X21”) = Sheets(“Data”).Cells(x, 2) Capture.Range(“Y21”) = Sheets(“Data”).Cells(x, 3) Capture.Range(“Z21”) = Sheets(“Data”).Cells(x, 4) count = count + 1 End If Next x End Sub I used to get error 424, now I get error "9" Subscript out of range, please help me out.
Sub searchdata() Dim lastrow As Long lastrow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row Count = 0 For x = 2 To lastrow If Sheets("Data").Cells(x, 1) = Sheets("Capture").Range("W14") Then Sheets("Capture").Range("W21") = Sheets("Data").Cells(x, 1) Sheets("Capture").Range("X21") = Sheets("Data").Cells(x, 2) Sheets("Capture").Range("Y21") = Sheets("Data").Cells(x, 3) Sheets("Capture").Range("Z21") = Sheets("Data").Cells(x, 4) Count = Count + 1 End If Next x End Sub
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 .
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
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.
Sir, I want to see all data like " Ram 256mb, Ram 1gb, ram 512 mb".. when I type "Ram" in search box its all see the "Ram" list which is in printable area..
Excellent question! Use this line of code: If Sheets("item_price").Cells(x, 1) Like "Ram*" Or Sheets("item_price").Cells(x, 1) Like "ram*" www.exceltrainingvideos.com/tag/using-wild-card-characters-in-vba/
Hello sir if we search in item code in B3 then shows Items in A11 . now how to create code if we search again then shows in A12 and again then A13. Please guide thank you
सर जी नमस्ते, सर क्या इसमें यह कर सकते है की किसी सेल में टाइप करे तो वो सारे नेम लेटर के हिसाब से फिल्टर हो कर लिस्ट आ जाए, और किसी भी नेम पर क्लिक करे तो वो डाटा जो हो जाए।
Hello sir, In this video I have checked the method ,but how can i get this details one excel file to another excel file. Pls share some idea about this. thank you
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
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!
Dear Dinesh Kumar Takyar
Greetings,
First of all thank you very much for this video
i just have a question What if there is 2 or 3 rows have the same item code and you want the search result showing all rows?
Thanks
+mostafa essam www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-vba/
thank you very much
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"
i like your videos so much, the method of your teaching is so simple and easy
Great Tutorials by Mr. Dinesh Kumar Takyar...............thanks
+vikas kumar You're welcome!
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
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?
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.
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.
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 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.
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
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
These links will help:
th-cam.com/video/8S4EdPJevlA/w-d-xo.html
www.exceltrainingvideos.com/search-multiple-values-in-worksheet-data/
Searching my channel and website would have given you more satisfaction!
Very good demonstration and good example to print one line at a time.
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?
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.
Thank you very much for the video Sir, it is very helpful and clear. Is there anyway, we can search multiple ITEM numbers instead of one. I really appreciate you help. Again, Thank you
This link should help: www.exceltrainingvideos.com/how-to-use-vlookup-to-get-multiple-values/
Dinesh Kumar Takyar , Thank you so much Sir. Your the best. we really appreciate your time and effort.
hi did you get any resolution for this issue
Hello Dinesh, your video was very helpful for a project I was implementing for work.
Thank you for that.
Many many thanks to you for prepare the my required excel software by your guidance. can i upload the software for your verifiction?
Well done. A real help. Clear presentation of the core ideas. Many thanks.
Thanks Dinesh. Question, why don't you use ws as a variable instead of spelling out the worksheet all the time?
Dim ws as worksheet: set ws = this workbook.worksheets("item_price")
Then you can just refer to it ad ws
Ws.cells(x,1)
krn14242 That's what I demonstrated in the second part of the video! Did you miss that?
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
Hi, this video was really helpful for my work.
I have however a question, I also want to populate A12, B13,C14 with other Item code without loosing the information already on A11,B11,C11.How could I do that, can you help? Many thanks,
Search www.exceltrainingvideos.com or show more details.
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?
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.
Thanks Dinesh sir u r simple & informative
Please share with your friends also. Thanks.
Thanx alot for useful information, but i have one question, if i have 4 items with the same name and i search for one, there should have next search option..
Farook Ahmed You can use count to find the total number of same items.
Dinesh Kumar Takyar Hello can you tell us how to use it in code??
Thanks
Good Morning Dinesh Sir, Myself i am working as if an accounts manager, i had tried your formula in vba it was really helpful , but i would like to ask you that if had same item name three times repeated with different rate and i want to display it one behind the other what should i do? i kindly request you to assist me with the code to apply as i am not good in VBA. Regards Shiju.
Once again Thanks for the knowledge you are giving to many peoples , may god bless you.....
Thanks sir,
also want to create 1 button in userform which shows two more button then I have to select one button of them
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/
Sir. it is useful for creating work flows. but in case of multiple records with same content, how to generate list using code
This link will help: th-cam.com/video/WvXOYeanIj8/w-d-xo.html
nice video sir, quick question how can i make my search result list all available instances and not just 1 instance? thanks
hello sir.. i think this one is suitable for our tiny district Emergency Department For Patient Registration.. but the problem is how to make a half A4 paper size for patient case note that can printable
You can enter the patient details in a specific way and for printing use page setup.
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.
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 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.
Thank you Mr Kumar, this information exactly what I was looking for, this is very helping my work
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
Informative and well presented as usual Dinesh. Thank you!!
Very clear video instructions - thank you so much!!!
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.
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
Mr Dinesh thank you so much for your tutorial..
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?
Is it possible to directly search data in the cell to google by using formulas. If yes can you please guide me how to do it. Or any other options to do it?
Can you give an example?
Dinesh Kumar Takyar
I have put the company name (Apple) in cell A1 and a social media name (Facebook) in B1 and i have got the result as ( Apple Facebook) in cell C1. Now i want to search this cell C1 in google but i want to do it with formula and not manually, as i am searching for many social media channels at one time.
Can you please guide me on this? or any other similar thing.
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.
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.
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!
Thanks for the video. I have a question on this , what if i want to use the item price file in the same manner but it is stored on sharepoint.
Please help
I'm not using SharePoint at present. But it should be possible.
@@Exceltrainingvideos i need to store the item price file on sharepoint , so that if the tool is in multiple PCs , we only update the sharepoint file and it can be used by everyone or is there any alternative to sharepoint for this.
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
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.
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...
I just copy your code and samples but it shows debug?
Copy/Paste will not work because some characters change. I have pointed this out in many of my videos.
Hello Sir. Thank you for videos function excel news .but could you lean to excel with file XML with VBA programming ?
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?
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
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 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.
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?
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
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
Valuable sir.
sir how can I search for Student Name , with Father Name and his Program
You can use multiple checks with 'AND' like so: IF A=B AND C=D AND D=E
Hello sir, I was wondering if there was a way to edit the data after you search for it, and have it be updated? Thank you.
This link should help: www.exceltrainingvideos.com/how-to-update-excel-worksheet-records-with-userform/
Dinesh Kumar Takyar j
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
sir, is there a way to search entire workbook other then worksheet?
Loop through all the worksheets in the workbook as shown in this link: www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
Alternatively search exceltrainingvideos.com
How are you sir? Amin from Dhaka Bangladesh
Thank you! I'm good. Are you enjoying my videos?
Share my videos with your friends in Dhaka and social media.
@@Exceltrainingvideos Yes Sir offcourse.. Your excel video tutorial as very nice. Simple clear understood your talking help me. Thanks Sir
Hello sir, thank you for a brilliant excel search function, is it possible please to search for data in columns rather than rows, thank you.
The cells property uses both the row and column indices.
You may like to search www.exceltrainingvideos.com for more solutions.
Hello Sir thank you very much for replying I love learning excel, and you teach very well,
What I am trying to archive is search for a part number say ABC123456-A01 this will then display the gauges needed for this part number, because the gauges cam be plug gauges with a tolerance from 3.750 - 3.3850 or a thread gauge M2,5 x 45 even core gages etc, I could type in the part number and will display all the gauges for this job, I altered you brilliant example to sort of work,
Hi Dinesh Kumar Takyar,
I like your videos and have tried to use this one however keep getting a 424 error and cannot for the life of me workout what the issue is. Are you able to help if I post the code?
Thanks Dale
+Dale Newman Check you code line by line. In case you copy and paste it here, I'll have a look.
+Dinesh Kumar Takyar - I am relatively new to this and have checked my code however couldn't find the error. I've posted below for your review and feedback. Thank you for such a quick reply.
Sub searchdata()
Dim erow As Long
Dim ws As Workbook
Dim lastrow As Long
Dim count As Integer
lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row
For x = 7 To lastrow
If Sheets("Data").Cells(x, 1) = Dashboard.Range("D6") Then 'This is where I'm getting the 424 error'
Dashboard.Range("C11") = Sheets("Data").Cells(x, 2)
Dashboard.Range("D11") = Sheets("Data").Cells(x, 13)
Dashboard.Range("E11") = Sheets("Data").Cells(x, 12)
End If
Next x
End Sub
+Dale Newman Try this code:
Sub searchdata()
Dim erow As Long
Dim ws As Workbook
Dim lastrow As Long
Dim count As Integer
lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row
For x = 7 To lastrow
MsgBox "The value is " & Sheets("Dashboard").Range("D6")
If Sheets("Data").Cells(x, 1) = Sheets("Dashboard").Range("D6") Then 'This is where I'm getting the 424 error'
Sheets("Dashboard").Range("C11") = Sheets("Data").Cells(x, 2)
Sheets("Dashboard").Range("D11") = Sheets("Data").Cells(x, 13)
Sheets("Dashboard").Range("E11") = Sheets("Data").Cells(x, 12)
End If
Next x
End Sub
+Dinesh Kumar Takyar - I tried your code and when I clicked on search a msgbox popped up and showed me the value in D6 and i could not close the msgbox. I had to control alt delete to stop it.
+Dale Newman - I took the Msgbox out and when I entered a value in D6 I didn't get the 424 error however no results showed in C11, D11 or E11. Just so I'm clear x, 2 x, 13, x, 12 are the columns I'm pulling data from?
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/
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/
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?
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: th-cam.com/video/8S4EdPJevlA/w-d-xo.html
It's a good idea to search my channel or website www.exceltrainingvideos.com
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
Hello Dinesh,
Thank you for your very helpful video! I keep encountering an error when I try to run the code in Excel 2010. I am getting a "runtime error 9 subscript out of range" message and when I open the Visual Basic debug it highlights the "lastrow" statement. Kindly help as this tool will be every beneficial to a project I am working on.... Thanks Again
+oscarpennstate Check your 'Sheet' value or name.
+Dinesh Kumar Takyar thank you for your reply, they are correct - I am stumped. Is it possible to kindly share your actual excel 2010 file from the video and then I can modify it to suit my project? I can provide my email address
+oscarpennstate Hi Dinesh, Please disregard my previous comment - However I have an additional question. Is there a way to search for only segments of a text string instead of an exact text string? For example if I have multiple item codes with the numbers "808" in the item code, can the search button return multiple results that have "808" in the item code?
+oscarpennstate Hi Dinesh - Any suggestions would be greatly appreciated - please help. Thank you
Now what if i have the same serial number with different quantities on different lines because they are in different locations, Is there a way i can have it search through the entire work sheet and have it give me the sum total of quantities? Thanks in advance.
Asset Intel This link with a sample file might help: www.exceltrainingvideos.com/counting-and-displaying-totals-automatically-using-excel-vba/
Dinesh Kumar Takyar I just ended up using a =sumif function to find the total values from multiple rows. Thanks again. I think i was just trying to put too much thought into it.... took me a little bit before i realized i could just use the sumif function to get me my numbers. Thank You!
Thank you for another great video. Have question for you sir, I have rows of data in sheet1 (lets say in column A, A1 is header). How can i calculate Average, Min and Max values in Sheet2 (lets say A2=average, A3=Min and A4=Max). There is no fixed numbers of rows on Sheet1, it could be data from A2:A300 or A2:A400. Please help as I have been trying this for a while.
Quoit1 =AVERAGE(Sheet1!A:A)
Dinesh Kumar Takyar Thanks for the quick reply Dinesh! Is it possible to use VBA macro for this? I have added a button, when clicked it should calculate average, min, max values from Sheet1 - store values on variables and then paste on Sheet2 (assigned cells, example A2, B2, C2). I am also thinking of using End(xlUp) to find the last row for better calculating values. My first row is a header, so i dont want to include first row in the calculations. Please guide me on this task. Thanks!
Dinesh Kumar Takyar
This is what I have so far:
Private Sub CommandButton2_Click()
Dim flow_a as Single
Dim mydata As Workbook
With Worksheets("Sheet1").Select
flow_a = Range("C2:C302")
End With
With Worksheets("Sheet2").Select
Cells(7, 8).Value = Application.WorksheetFunction.Average(flow_a)
End With
End Sub
I am getting "run-time error: Type mismatch" on line "flow_a = Range("C2:C302").
Very nice and usefull. Great
Glad you liked it!
The data I use, for one ID number exists more than one search result . How to display them all in another sheet ?
Hi Sir, could u please show us to display a particular cell value in note pad..just to display and not to create a notepad file..
This link will help: www.exceltrainingvideos.com/how-to-automate-transfer-of-excel-data-to-notepad/
Nice tutorial...
Thank you sir Dinesh Kumar Takyar.
How can I modify this code to read from an excel data table rather than cells?
What will be the changes in the code if we use more than one criteria
You can use 'AND' for multiple true criteria and 'OR' for any true criteria. This link will guide: www.exceltrainingvideos.com/automate-date-validation-using-vba/
Or search www.exceltrainingvideos.com
thanks a lot sir....really appreciate your effort .....nicely done....
Hello, regards from Mexico,
This code is so useful for what I'm trying to do, and I'm forcing myself to resolve the next issue because I just don't seem to understand why this wouldn't work, my sheets are called "Data"(where the list comes from) and "Capture" (where I want it to be printed), here's the code as I modified it, I hope you can help me this is driving me insane
Sub searchdata()
Dim lastrow As Long
lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row
count = 0
For x = 2 To lastrow
If Sheets(“Data”).Cells(x, 1) = Capture.Range(“W14”) Then
Capture.Range(“W21”) = Sheets(“Data”).Cells(x, 1)
Capture.Range(“X21”) = Sheets(“Data”).Cells(x, 2)
Capture.Range(“Y21”) = Sheets(“Data”).Cells(x, 3)
Capture.Range(“Z21”) = Sheets(“Data”).Cells(x, 4)
count = count + 1
End If
Next x
End Sub
I used to get error 424, now I get error "9" Subscript out of range, please help me out.
Sub searchdata()
Dim lastrow As Long
lastrow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
Count = 0
For x = 2 To lastrow
If Sheets("Data").Cells(x, 1) = Sheets("Capture").Range("W14") Then
Sheets("Capture").Range("W21") = Sheets("Data").Cells(x, 1)
Sheets("Capture").Range("X21") = Sheets("Data").Cells(x, 2)
Sheets("Capture").Range("Y21") = Sheets("Data").Cells(x, 3)
Sheets("Capture").Range("Z21") = Sheets("Data").Cells(x, 4)
Count = Count + 1
End If
Next x
End Sub
When you copy and paste the code, observe the quotes(") and edit them if necessary.
What shoud I do if I want to search multiple times and make a list of searched data to print out?
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 .
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
Thank you so much for this video!!!
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.
Sir, I want to see all data like " Ram 256mb, Ram 1gb, ram 512 mb".. when I type "Ram" in search box its all see the "Ram" list which is in printable area..
Excellent question! Use this line of code:
If Sheets("item_price").Cells(x, 1) Like "Ram*" Or Sheets("item_price").Cells(x, 1) Like "ram*"
www.exceltrainingvideos.com/tag/using-wild-card-characters-in-vba/
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
Hello sir if we search in item code in B3 then shows Items in A11 . now how to create code if we search again then shows in A12 and again then A13. Please guide thank you
Use the concept of the nextblankrow or lastrow like so:
erow = ws.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Dear Sir, is it possible to get current printer name in excel cell / form text box though formula or VBA function. please help
सर जी नमस्ते, सर क्या इसमें यह कर सकते है की किसी सेल में टाइप करे तो वो सारे नेम लेटर के हिसाब से फिल्टर हो कर लिस्ट आ जाए, और किसी भी नेम पर क्लिक करे तो वो डाटा जो हो जाए।
www.exceltrainingvideos.com/filter-data-in-combo-box/
सर जी नमस्कार क्या आप छोटे छोटे साफ्टवेयर बनाकर भेज सकते हैं
Hello sir,
In this video I have checked the method ,but how can i get this details one excel file to another excel file. Pls share some idea about this. thank you
www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/
You can also search at www.exceltrainingvideos.com
i keep getting a feedback "lastrow = Sheets("item_price").Cells(Rows.count, 1).End(x1Up).Row" what should i do?
Thank you Sir, Clearly explained in details and widly applicable.
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