How to use the Find and FindNext methods in VBA to search for data in an Excel worksheet quickly and easily. More details available at this link: www.exceltraini...
Once again, you delivered a valuable lesson with a perfect blend of articulation, precision and pithiness at a slow pace that can be increased, using UA-cam's control, to suit our individual preference; great job!
Dear Dinesh Kumar Takyar, Thank you for your great assistant and support, sir my listbox shows 2 column (customer name & customer payment) and both of them based on invoice sheet - I want to select more than 1 customer from my listbox which is built on userform (MultiSelect) and calculate the total payment for each of them using sumifs function (on the basis of the customer name column in my listbox) then display the result for each customer in the same msgbox in succession. Can you advice on the vba code to set this condition? Thanks alot in advance
Hi Sir, Thanks for the lesson. its really valuable, i just have one query here , if i have a item in column A and a blend of string in column B . How can i extract specific data from that string corresponding to item in column A and save it in sheet2
Sir in in salary column suppose we have numbers say 1,2,15,1,16,31 and if I search for number 1 it is showing all columns except column having number 2. Please guide me sir
I have tried your vba code to find the next empty cell on my workbooks but! it does not work, because my workbooks I am working on all have a combination of formatted and conditional formatting in my cells as well as I have a clear button set up to clear the contents in the range, also the copied area (range) in my workbook/sheet has formulas built in the cells, the paste option in my vba code is PasteSpecial xlPasteValues, I am using microsoft office 365 subscription, so please at your earliest convenience let me know the solution, thank you, happy new year. Ps forgot to mention the error message I get is Application-defined or object-defined error or Subscript out of range (Error 9)
Most probably the worksheet you are referring to doesn't exist. Anyway, you can try: lastrow = Application.WorksheetFunction.CountA(Sheet1.Range(“A:A”)) Change the sheet name accordingly.
Hi, Thank you very much for this tutorial, which is very well explained I have a question: how would you modify the code to display "John" and "Harry" instead of "B2" and "B5" please ?
hi i have found list in column (A) of name this name is repeated several times, I am looking for a code that I fill cell in column (B) with first found if is empty is only once for second time I fill second empty cell these values by textbox
Hi Dinesh, thank you so much for presenting this solution, I had been struggling to get the find next working in my Visual Studio application but this example works perfectly, again thank you for sharing
Thanks much Dinesh.. I have two sheets. I need to loop through all cells in Column H to lastrow and for each cell find the same value in sheet2. Note that sheet 2 also has account numbers in column G. There could be multiple rows in sheet two for the same account. When each row is found in sheet 2, I then need to copy the entire row and insert below its corresponding row in sheet 1. Could you help with this code?
+Douglas Hayden This link might help find a solution: www.exceltrainingvideos.com/transfer-specific-excel-worksheet-data-range-from-one-worksheet-to-another/
+Dinesh Kumar Takyar Thanks much.. It was helpful in pointing me in the right direction. I will make modifications as needed... Commendations are in order for your outstanding videos.
thanks you for this video sir. I tried requesting the value of firstaddress with : firstaddress.address or firstaddress.value in the immediate window and I get an error saying object required. why is this?
Dear sir I have the sales data like categeory wise customer wise invoice date wise week wise sales value I required out put is to find out the new customers billed in week 2 comparitive last week ie week these customers not billed in week 1 .
Hi ive been watching and learning alot from your videos. However on this particle one ive created a Workbook that has 3 Sheets on them. my code breaks down immediately after Set oWkSht=Sheets("Sheet3"). The error I get is that the subscript is out of range. Under Excel Objects, the Sheet3 (Order Registry) is the name. Ive tried changing the "Sheet3" to Order Registry but I think ive done something wrong somewhere. can you please advise. thank you
Hello sir This helps me to save 25% of work time I'll u pls let me know how to get the refence number to put on "D collum" instead of a message box 2nd thing is the is it possible to find multiple duplicate data, put the reference number in D cell as well as, instead of one specific data can u pls help to find multiple duplicates
Very useful video tutorial. Sir, Is it possible to give me your email? I did finding matches on VBA And i have done almost 90% but there is a little problem with putting colors. Thank you.
Dinesh Kumar Takyar i'm sorry my English so bad,. i'm from Indonesia,. i see tutorial use find & find next on UA-cam,. is a work at modul,. but if i have 1combobox, 1commadbutton. 6textbox how to command button click, find what=combobox1 and value to 6textbox thank you sir
please help this formula sir dim C as range dim i as string i=combobox1 on error resume next with worksheets("sheet1"). range ("A1", range ("A1").end(xldown) set c=.find(what:=i, lookin:=xlvalues) if not c is nothing then firstaddress=c.value do textbox1.value=c.offset(0,0). value textbox2.value=c.offset(0,1).value textbox3.value=c.offset(0,2). value textbox4.value=c.offset(0,3).value textbox5.value=c.offset(0,4). value textbox6.value=c.offset(0,5).value set c=.findnext(c) loop while not c is nothing and c.valuefirstaddress end if end with end sub thank you sir
Once again, you delivered a valuable lesson with a perfect blend of articulation, precision and pithiness at a slow pace that can be increased, using UA-cam's control, to suit our individual preference; great job!
Thanks Dinesh!... It was very useful for my project(JAKARTA, INDONESIA)
Glad it helped! Don't forget to share the Excel VBA tutorials with your friends. Because sharing is caring!
very helping and useful video.. i was searching this since many times.... thanks
Very well explained!
Dear Dinesh Kumar Takyar, Thank you for your great assistant and support, sir my listbox shows 2 column (customer name & customer payment) and both of them based on invoice sheet - I want to select more than 1 customer from my listbox which is built on userform (MultiSelect) and calculate the total payment for each of them using sumifs function (on the basis of the customer name column in my listbox) then display the result for each customer in the same msgbox in succession. Can you advice on the vba code to set this condition? Thanks alot in advance
Thank you very much sir. It's a great learning to me.
Glad to hear that. Please share my Excel VBA tutorials UA-cam channel with your friends too. Thanks.
Hi Sir,
Thanks for the lesson. its really valuable, i just have one query here , if i have a item in column A and a blend of string in column B . How can i extract specific data from that string corresponding to item in column A and save it in sheet2
You can use nested loops like so: www.exceltrainingvideos.com/nested-do-while-loop-instr-function-in-excel-vba/
Sir in in salary column suppose we have numbers say 1,2,15,1,16,31 and if I search for number 1 it is showing all columns except column having number 2. Please guide me sir
I have tried your vba code to find the next empty cell on my workbooks but! it does not work, because my workbooks I am working on all have a combination of formatted and conditional formatting in my cells as well as I have a clear button set up to clear the contents in the range, also the copied area (range) in my workbook/sheet has formulas built in the cells, the paste option in my vba code is PasteSpecial xlPasteValues, I am using microsoft office 365 subscription, so please at your earliest convenience let me know the solution, thank you, happy new year. Ps forgot to mention the error message I get is Application-defined or object-defined error or Subscript out of range (Error 9)
Most probably the worksheet you are referring to doesn't exist. Anyway, you can try:
lastrow = Application.WorksheetFunction.CountA(Sheet1.Range(“A:A”))
Change the sheet name accordingly.
Hi,
Thank you very much for this tutorial, which is very well explained
I have a question: how would you modify the code to display "John" and "Harry" instead of "B2" and "B5" please ?
This link will help: www.exceltrainingvideos.com/find-and-extract-all-specific-values-from-excel-data/
hi i have found list in column (A) of name this name is repeated several times, I am looking for a code that I fill cell in column (B) with first found if is empty is only once for second time I fill second empty cell these values by textbox
Hi Dinesh, thank you so much for presenting this solution, I had been struggling to get the find next working in my Visual Studio application but this example works perfectly, again thank you for sharing
Glad it helped. Please share with your buddies too.
You saved my day thank you
Glad to hear it
Thanks much Dinesh.. I have two sheets. I need to loop through all cells in Column H to lastrow and for each cell find the same value in sheet2. Note that sheet 2 also has account numbers in column G. There could be multiple rows in sheet two for the same account. When each row is found in sheet 2, I then need to copy the entire row and insert below its corresponding row in sheet 1. Could you help with this code?
+Douglas Hayden This link might help find a solution: www.exceltrainingvideos.com/transfer-specific-excel-worksheet-data-range-from-one-worksheet-to-another/
+Dinesh Kumar Takyar Thanks much.. It was helpful in pointing me in the right direction. I will make modifications as needed... Commendations are in order for your outstanding videos.
Hello, how to find a formula?
great video, thank you very much for clear guidance
Thanks for the feedback.
thanks you for this video sir. I tried requesting the value of firstaddress with : firstaddress.address or firstaddress.value in the immediate window and I get an error saying object required. why is this?
Check your code especially the variables.
Dear sir I have the sales data like categeory wise customer wise invoice date wise week wise sales value
I required out put is to find out the new customers billed in week 2 comparitive last week ie week these customers not billed in week 1 .
Hello,
Would it be possible to have it return the entire row associated with the search criteria?
This link will help:
www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-vba/
Hi ive been watching and learning alot from your videos. However on this particle one ive created a Workbook that has 3 Sheets on them. my code breaks down immediately after Set oWkSht=Sheets("Sheet3"). The error I get is that the subscript is out of range. Under Excel Objects, the Sheet3 (Order Registry) is the name. Ive tried changing the "Sheet3" to Order Registry but I think ive done something wrong somewhere. can you please advise. thank you
+Donovan Newton Sheets("Order Registry")
+Dinesh Kumar Takyar I tried that and the same issue still comes up.
Hi can you show us how to write the VBA to find column B valve > than for example > than 10000
Thanks a lot i had use this lesson for my project
Wildly helpful, thank you sir
Thanks Dinesh! It was very useful
Great video. Why did you not declare firstaddress
Not required to declare all variables provided you have Option Explicit turned off. Excel usually can figure out how you want to use the variable.
Hello sir I want to make download and upload file button option for any type file such as PDF Docs and Jpeg in excel seet.
please give me solution.
+Thanks Tutorial Library Give an example.
Hello sir
This helps me to save 25% of work time
I'll u pls let me know how to get the refence number to put on "D collum" instead of a message box
2nd thing is the is it possible to find multiple duplicate data, put the reference number in D cell as well as, instead of one specific data can u pls help to find multiple duplicates
Useful video. Thanks a lot.
Very useful video tutorial. Sir, Is it possible to give me your email? I did finding matches on VBA And i have done almost 90% but there is a little problem with putting colors. Thank you.
takyardinesh@gmail.com
Great video. Thank you Sir
+Samir ALGIERS Glad to hear that!
Too good Sir. Thanks
hello sir,. how using this formula to userform
Which formula?
Dinesh Kumar Takyar i'm sorry my English so bad,. i'm from Indonesia,. i see tutorial use find & find next on UA-cam,. is a work at modul,.
but if i have 1combobox, 1commadbutton. 6textbox
how to command button click, find what=combobox1 and value to 6textbox
thank you sir
please help this formula sir
dim C as range
dim i as string
i=combobox1
on error resume next
with worksheets("sheet1"). range ("A1", range ("A1").end(xldown)
set c=.find(what:=i, lookin:=xlvalues)
if not c is nothing then
firstaddress=c.value
do
textbox1.value=c.offset(0,0). value
textbox2.value=c.offset(0,1).value
textbox3.value=c.offset(0,2). value
textbox4.value=c.offset(0,3).value
textbox5.value=c.offset(0,4). value
textbox6.value=c.offset(0,5).value
set c=.findnext(c)
loop while not c is nothing and c.valuefirstaddress
end if
end with
end sub
thank you sir
This link will guide: www.exceltrainingvideos.com/how-to-get-data-from-worksheet-into-combobox-and-textboxes-on-user-form/
thanks. Vôlnei (Brazil, Rio de Janeiro)
Thanks Dinesh. Little quieter this time. Good traffic day I guess. Lol
+krn14242 Better editing this time!
hi/ Dinesh Kumar Takyar
good day to you
you are good man
hope to you all best
+Adam Arbab Thank you!
+Dinesh Kumar Takyar thanks you too, good luck