EAF #37 - Excel VBA Loop to Find Records Matching Search Criteria

[@briansullivan1491]

I love KZbin for learning everyone's VBA design perspectives. I'm always looking at code in unlimited ways.

[@hustlinhitch]

After watching other videos and still feeling confused, you explained it so much clearly. Thanks!

[@trdn1]

Thank you times a million! I was eyeballs deep trying to decipher array formulas, when I finally found this video. So simple, and explained so well. I was able to take your example and apply it to my unique case. Screw arrays! viva VBA!!!

[@ExcelTricksforSports]

Awesome to hear you has some success. My code is not perfect but if you can use it make things work for your problem then you have done well. ExcelVBAIsFun is a great channel to follow if you want to learn more VBA in a friendly way.

[@KamiliGirl]

you don't know how this tutorial helps me in my work. Although I'm not a programmer, but I was able to adapt it to fulfil my needs. Your explanation is excellent and clear for beginners such as me, Thank you for sharing your knowledge and this tutorial.

[@samdesai27]

Hi have you done a video for copy row data based on one condition but only selective cells? For example copy row if F contains YES but only copy Col A, B and T

[@2nfranz149]

Sub finddata() '1. declare variables '2. clear old search results '3. find records that match criteria and paste them Dim athletename As String Dim finalrow As Integer Dim i As Integer 'row counter Sheets("Data").Range("P5:Z50").ClearContents athletename = Sheets("Data").Range("P2").Value finalrow = Sheets("Data").Range("A10000").End(x1Up).Row For i = 2 To finalrow If Cells(i, 1) = athletename Then Range(Cells(i, 2), Cells(i, 12)).Copy Range("P100").End(x1Up).Offset(1, 0).PasteSpecial x1PasteFormulasAndNumberFormats End If Next i Range("P2").Select End Sub

[@kyheofarquharson524]

I'm super new to this, but I think you have: finalrow = Sheets("Data").Range("A10000").End(x"#1"Up).Row instead of: finalrow = Sheets("Data").Range("A10000").End(x"L"Up).Row either way, thank you so much for sharing the code

[@shakerswits5833]

Thanks for the video. It's very insightful. How do I return those data to text boxes in VBA userform?

[@mrnascimento3]

It's an amazing video. I'm learning VBA and fill happy when good people spread knowledge. thanks

[@MrMoonwalkingBear]

Your keyboard honestly sounds amazing oh my word

[@NoelleMcFarlandMusic]

This has been very helpful. This is the first code I'm trying to write from scratch. I've got it return what I want but only the first record where there should be 20+. Can't figure out why the loop or paste range isn't working. Help? For i = 2 To LRow If Cells(i, 1) = Part And Cells(i, 3) > Cut And Cells(i, 4) = "" Then Range(Cells(i, 1), Cells(i, 3)).Copy Worksheets("Multi Cut Lengths").Activate Sheets("Multi cut lengths").Range("F35").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats End If Next i

[@cminoiu]

Thank you for sharing this very helpful code! If I would want to put the code onto a different sheet, how do I make VBA look for data on a different sheet? Thank you!

[@Evering000]

Hi, this Video is very helpful. I have another question, how will be the code if I want to copy the search results in another sheet in this workbook?

[@elenanoe420]

Hello ExcelTricksforSports, Your video is excellent. After hours of anguish I have finally found a code that works for mini-project. Grazie mille!

[@ktea8643]

This was very helpful. It helped a lot! Thank you for such a detailed description and making it so easy to follow.

[@2015-j8e]

Thank you... very clear, concise and informative. keep up the good work.. This is what I was searching for...to look all the records that match criteria...

[@alexeiofp]

THANKS A LOT U GAVE ME A NEW IDEA, SALUDOS DESDE SANTIAGO DE CHILE.

[@lavdonbela2479]

how do i code so that every time i search data i want it to be save on the P2 till P50 , so that i can create a table over time each time i search. Thanks for your help

[@scottlytle5244]

I just stumbled onto this tutorial and it is perfect for a project I am working on! Thanks so much!

[@shahzadisrani2616]

Sir it is very good video but my question is, instead of finding name if we put criteria in Sheet(2) ABC=10001 to ABC=10010 find data from sheet(3) & it will find & when click button on print, it will auto print on format Sheet(1) by 1 by 1 till range given. Kindly suggest how to do this.

[@pressytocontinue3569]

This worked well however it will not find an entry in the spread sheet that is a lower Excel spread sheet number than the current active record row number found in the search. For example, if the first seach if for "Seema" (row 9) the Word document is populated with the correct data. If a second search is performed for "John" (row 4) then the data is not displayed. Is there any way to reset the search to start at the beginning of the spread sheet each time getdata is run?

[@bushchat28d]

Hard coding values with guesstimates when they could be found by code is a bit weird and means that it will not necessarily return the correct results with large datasets. Similarly, removing a pre-defined amount of 'previous results' could mean that some are left behind...

[@7amana]

Excellent Job, God Bless you. I tried it on the same sheet it works fine but where as changed the output to another sheet it is not working. Can't understand where am I doing a mistake.

[@quincyirang2543]

Hello, I want to know, what if the database is at sheet 2, and I want to display the data at sheet1 ?

[@mxinss7917]

Hi, this is very useful, I am facing a situation where I have to constantly copy and paste data from one workbook to another where I can't make changes to the originals raw data file or the one I am posting it to. both files are in chart format 1) Instead of matching 1 criteria as you have showed, I need to match 3 criteria (year, model, type) to the posting workbook requirement which are all different in orders (thus simple copy and paste cannot work) 2) Multiple (10+) columns titles needs to be matched between the raw data workbook and the posting workbook since not all row data is required for reporting purpose I hope you are able to help, thank you!

[@ExcelTricksforSports]

Yes this kind of copying and pasting works great with VBA

[@RohanK]

Hey first of all Thank you very much for taking time and making this video for us. I am getting "Variable not declared" After Running the code for line "finalrow = Sheets("database").Range("A1000").End(x1Up).Row" Can you tell me what's wrong with this line It specifically highlights .End(x1up). from that line says it has to be declared.

[@caseyturner5549]

I'm knew to this stuff and hope you still respond to comments on a post this old. Is there a way I could insert this macro into a cell so if I just push "enter" then it will automatically run the sequence without the use of the button?

[@JohnLythe]

Yes, macros can run on a change event such as entering something into a cell - search google for "Excel VBA change event" and you should find some good hits

[@p.supraja9797]

Thank you for this session . It was of great help for me .. This macro is not working if there is a change in format of the search criteria . Like capital letters and small letters in the search criteria is different . Thanks for the help in advance

[@rpsoni8646]

Sir Excellent video My search Dim is a Date, an integer, Will the code work? Or it is for String? Regards

[@ladgrove]

This was very helpful, but am I wrong in thinking that what we've done here is just make a manual pivot table?

[@shivanandgondhlee7728]

Hi, I need your help..in time sheet data how to indefine how not filed time sheet and get report

[@kaidson]

Thank you, your video help me a lot. Due to your formula, i had modified to get data from others workbook. I am not an expert, some how i manage to run the system smooth and easy.

[@IAMVIDITVERMA]

Did you use Data validaton in Athlete names? How did u extract unique names ? Pls reply

[@SimranKaur-hp6nl]

Thank you so very much it was very helpful!🤍🤍

[@benjamintrevitt381]

I've been looking for something exactly like this, but problem is I followed your instruction which I thought were set plain and simply to understand but I can;t seem to find where I'm failing. Can you help as this is perfect for what I want to achieve?

[@swastiklandsman8606]

Hi, I'm trying to adapt the VBA code from into a loop. The aim is to copy and paste a range of cells (Name Range "Output") for each scenario that I'm running into a numbered list (Name Range "Paste.Index"). There are 100 scenarios that are numbered 1-10 (Name Range "NumScenarios") and whichever scenario is chosen is shown in the cell (Name Range "ActiveScenario"). My intention is for the macro to cycle through each of the 10 scenarios and then copy and paste values for each scenario based on the location in the list for that scenario. So for example, if scenario 5 is active, then the macro would copy "Output" and then paste the values next to where the number 5 appears in a list. For some reason the code refuses to paste anything past scenario 6 and it pastes over some of the older pasted values for some reason. Any help would be greatly appreciated.

[@valentinakoleva936]

This was sooo helpful! Loved the accent as well. :) Just one question - how do I change the code if I want to create a copy of the original data in a separate sheet (what you did for P5:Z50, I just need it on a new sheet) ?

[@gerardvaneggermond9967]

Hello I try to learn VBA, maybe you can help me with a rather complex problem. In the worksheet "Menu" I have in cell "E2" I have data from 1 to 3, now I have cell "B4" and "B10" I have an ID reference to names (there are 20), what do I want now do, for example. if E2 = 1, I want to go to worksheet "1" then look at the IdNr then put the data in the right place. It contains date for "B4" = cell "C6", "E6", "E8". for "B10" = cell "C12", "E12", "E14". So with vertical search and a loop, maybe the next formula. if "E2" = 1 then Workbook 1 if B4 = 1 then and B10 = 2 then Hlookup (B4, workbook 1 (A1: CV21), 1) lookup (B10; workbook 1 (A1: CV21), 3.3) C6 = workbook 1 (places in column 3 row 3) for each number in b4) E6 = workbook 1 (places in column 5 row 3) for each number in b4) E8 = workbook 1 (places in column 4 row 3) for each number in b4) if B10 = 2 and B4 = 1 then Hlookup (B10, workbook 1 (A1: CV1), 1) lookup (B10; workbook 1 (A1: CV21), 3.3) C6 = workbook 1 (places in column 8 (is also column 3 of "B10") row 3) for each number in b4) E6 = workbook 1 (places in column 10 (is also column 5 of "B10") row 3) for each number in b4) E8 = workbook 1 (places in column 9 (is also column 4 of "B10") row 3) for each number in b4) So maybe it's useful to work with a loop (I do not know!) I do not know if this formula is correct. It would be useful to (search on both B4 and B10) than put the data in the right place, can this, can you help me?

[@CherAmi1024]

Does this code only work to populate a new table of numbers/integers? Can I use this code if i'm trying the same operations but with names or other attributes?

[@limck9248]

hi, if i want to add another drop down to search another information example by specific data, how would the code be?

[@kanyocsaba]

Hi, The video was very useful for me. I've done the job what I want. But I have a new one now. Pretty much I want to use exactly the same loop search but for just part word in the cell. Let say on your sheet you would have more than 1 Alex firstnames and want to search through the name range and you want to pull out all these same firstnames to the list on the target range. I hope you know what I mean. In my case I have a range with more than 4 words usually. I desperately looking for the right way but no success so far. Thanks for your help in advance!

[@MrTaeko22]

Thank you for the vid. This was really good. Just have one simple question: The method for copying and pasting the range (before the "End If" in the loop). Why does the macro refresh the list of ranges that were already copied over from the previous name? Based on the logic it would seem that if you changed the lookup name, the macro would copy the new ranges underneath the previously copied ones.

[@MrTaeko22]

Oh okay, nvm I see now. The clear content clears it after the procedure has ended. Totally missed that.

[@porter444]

I really like this, but I am having trouble getting it to work correctly. When I run the macro the search returns (pastes) all values from the table (not just those for the name I specify in P2). My data is in A2:L482 column A contains the Name. finalrow = Sheets("Data").Range("A500").End(xlUp).RowI built a list of unique values and cell P2 is a data validation list of those unique values. The max number of matches for one Name is 31 lines of data. Range("P100").End(xlUp).Offset(1,0).PasteSpecial xlPasteFormulasAndNumberFormatsSearch result headings are in P4:Z4.Any ideas what might be wrong? I am using Excel 2013

[@Nitishrajpal]

Is it possible to integrate excel spreadsheet into a webpage and perform all these functionalities from there..? I'm stuck with this project thing and couldn't find much about it.

[@mout001]

Every Month I need to copy a range of data from one workbook to another workbook. In the column are the Months (Starting from April 18 till May 21). In the source file the Months are in row 2 (starting from E2, F2, G2, etc.) In the active file the Months are in row 5 (from CJ4, CK4, CL4, etc. till DU4).When the Month is ended I need to copy a range of 27 cells (row 3 till row 29) from the source file into the active file the column is based on the selected Month in cell (CI2) from the active workbook. The range is always 27 cells.So in the active workbook, I first select a Month (e.g. Aug 18) and then I click on a button to activate the copy function.At the moment I used IF, and ElseIf functions but this is a very long code. Can you help me to program a combination of a Vlookup with a loop function or something. There must be a much easier way to do this than I do now...right? Active Workbook Source file

[@prashantmathur1206]

Done the similar work.. but code is not working at all..!! Did you use Data validation for drop down of athlete name?

[@zulgamingtv]

nice video. hello can i know how you extract all the unique names in column N from column A using Vba?. thanks

[@bahman91bl]

hey bro, thanks for your video, but i have problem with the Finalrow """Finalrow = Sheets("Data").Range("B1000").Row.End(xlUp)""" since i have all the names in Column B, What should I do Thanks.

[@barrelspassages287]

Excellent solution and explanation, exactly what I need! Thanks a million for your help, you get a new subscriber 😀!

[@francesturiano6062]

A quick question sir, how did you do the "count" (Q2) and sort the "name" (P2)?

[@JohnLythe]

The count is just a formula in the cell (not done with VBA). The picklist is made using Data Validation and points to the range of cells in column N. If you email me i can send the file (address is in the video at 11:20)

[@deoncatindig6242]

can you use this in a sort like users will input the criteria then there is a button that will generate what the criteria inputted?

[@shashikalagangappa4735]

hi sir i have a doubt in some column used specific codes in another column used more than 1 unites for particular account here how to find if more than 1 unites used for specific code in particular account please help me

[@limratanak1716]

thank you very much. this video is really helpful. it's really help me a lot.

[@prashanthkumar7666]

Hello I keep receiving this error when ever i got to do a search. can you please tell me how I can fix this? this is the error (object doesn't support property or method )

[@yvonnedong947]

Thank you! Really helpful for VBA beginners

[@augustocharles49]

Hi How can I do the same but this time scanning ITEM and finding record in my database. Thank you

[@JohnLythe]

You will just need to align the item you are searching for to the correct column of data. The code is relatively simple to modify.

[@morganeguilluy7765]

Thanks for ur clear explanation ! One last question. I have several values as filters. Meaning in ur example i will have value from P2 to P4. In the code im trying to adapt the line athletname=Sheets("Data"). Range("P2:P4"). Value but doesnt work. Any help is welcomed :) thanks !

[@morganeguilluy7765]

Just to bring one precision. Doing a list will not help me since in real case i have up to 200 data in my list of filter.

[@MotoGeeCee]

how to do it if it contains part of the text? like if you want to display all data that contain "Dar" for names darwin, dana,darla?

[@TianH1366]

Thank you so much for sharing these useful skills!

[@ivosantos5322]

Hi, this works fine for me, but now i need to match more than one cell, how can i do that?

[@mohajeran]

Hi All; I have a excel sheet with some calculations. I need to increase cell A1 by 1. then I need to check the values of C1 to C10. If one of the C1 to C10 cell has a value between -1 and +1, then my A1 has reached its maximum value, otherwise I have to increase A1 by 2, and check again C1 to C10 to see which of these cells holds a value between -1 and +1. Could you help me with this ? Thanks a lot. Cheers Mike

[@HartGekochteEierProj]

immensly useful , going to walk through your videos...cheers!

[@ricalgen]

Can you please help me how to convert the code on ms access using excel as data base?

[@atlokblog2286]

Dear ExcelTricksforSports, If I wanna match more than one cell, what should I do? (I mean multiple criteria)

[@ritamatheunis]

take a look at this - kzbin.info/www/bejne/d2OWhGSvl62dgMk and use it like this Sheets("DB persoonlijke gegevens").Range("B3:H443") .AdvancedFilter Action:= _ xlFilterCopy, _ CriteriaRange:=Sheets("DB persoonlijke gegevens").Range("AX3:AY4"), _ CopyToRange:=Sheets("Tijdelijk").Range("B1"), Unique:=False

[@mfkalabdullah6966]

Hey how I can keep the Loop continue showing the result for first and second row to etc. Is there any code for that thanks a lot

[@chhansambath5244]

How make it work in other sheet?

[@JohnLythe]

Check out video 78, there are 5 examples that might be useful kzbin.info/www/bejne/fqqynpaYfa6cZtk

[@chhansambath5244]

the code to complicated , do you have something simple as #37? but can search in different sheet and can choose data which column can show from main data ?

[@kellydwyer8553]

What if we have our source data stored in another sheet? I can't figure out the code we should use to copy and paste

[@JohnLythe]

kzbin.info/www/bejne/Y4Kpm32lh6x2qrc

[@JohnLythe]

Video 78 shows this

[@Dopeboyz789]

Also how do you match it with numbers instead of letters

[@mikelennon1078]

Does anyone knows how to create an Excel Macro that Searches Entire Workbook and Returns All Records which match a certain value from a drop down list? e.g. if we have a list of many large organization in a worksheet and have a list of all of their employees ( many per organization) in another worksheet. How we can see the records of all employees in an organization by selecting an individual organization from a list or drop down box, etc. I would appreciate if your insights.

[@Dopeboyz789]

How do u use it in a formula instead of vba

[@vasilisg.2637]

God bless you!!! Yoo help so so so much!! Thank you!

[@TipsAndTricksGuy]

Thanks so much! Simple, easy to understand and easily adaptive code! Good stuff :)

[@sacca9156]

can i obtain the worksheet in excel so that i can follow your tutorial

[@tommypeter7335]

Dear Team, I need to write a VBA as i have worksheet with different values in in different date and i required if i mentioned a date it should pick up the values of that particular date in below mentioned format: Date Outlet Check Number Item Code Item Description Qty Value Remarks Operator

[@Dopeboyz789]

What if it p2 ,q2 and r2. How do you write it in vba?

[@natelikesvids]

This code works well. I was wondering if there is a way to adapt this so that instead of finding an exact value, it will find a partial value like instead of typing "John Doe", I can just type "John" and it will return everyone named Joe. Thanks in Advance for any help.

[@natelikesvids]

I just realized i made a mistake, *john not joe

[@ExcelTricksforSports]

Natee123 Yes this would be possible. If it is a simple search like you mention i.e. looking for "John" then the use of the "In String" function would be the best option. The Microsoft Excel INSTR function returns the position of the first occurrence of a substring in a string. If you google excel VBA INSTR you will get some good hits with examples of how to proceed

[@natelikesvids]

ExcelTricksforSports Thanks, I will give it a shot.

[@gilokye2745]

Thanks for the Lesson, could you please show how the code will look like if you are working between two workbooks. eg. one workbook will be where you will keep and maintain the data and the other is where people will go, type in a particular item # to extract all the information pertaining to that item #. THANKS

[@robtwentythree]

Could you use this same macro but search by the dates in the 2nd column instead of the names?

[@ExcelTricksforSports]

Yes, you can search in any column or even use two criteria e.g. name and date. The code just needs a little editing. See also a more recent video #78 which replicates this one with some different scenarios

[@rpsoni8646]

ExcelTricksforSports Sir Date does not work

[@tonbergman4339]

Excellent video my gust ion max rows. 30 000. When I have more rows what to do Thanks for you help. ton

[@vipulp4nd3y36]

hey, I have a code written to delete data from X column if the value in the column is "delta" and it's not working. Can you help me with it please?

[@vipulp4nd3y36]

Please reply on [e-mail] thanks

[@vipulp4nd3y36]

This is the code Sub removeCS() Dim sheet As Worksheet Dim C1row As Long Dim Value As String Dim C2TotalRows As Long Dim NoDups As Long Set sheet = Worksheets("Remove") C2TotalRows = 50 Value = "Delta" For C1row = 2 To 10 If Value = sheet.Cells(C1row, 22).Value Then sheet.Activate Rows(C1row).Delete NoDups = NoDups + 1 C1row = C1row - 1 Exit For End If Next C1row = C1row + 1 MsgBox NoDups & " Entries were removed" End Sub

[@sandrotsomaia7332]

you should use Cells(Rows.Count,1).end(xlup).row for the last row :) that is the better you u should learn people :)

[@sarahlee6262]

How to obtain the Count?

[@2nfranz149]

Is there something wrong with the codes? its showing variable not defined. (x1Up)

[@Epritsolutions]

Anton France Munoz the code looks slightly wrong. (xlup) = XLUP not it is L not 1(one)

[@clamgoh]

Thanks for sharing , is very useful

[@manikandanraja3386]

I need both string and integer as a find data how give dim code

[@RohanK]

Hey I got where I was wrong just after commenting I wrote End(x"one"Up) instead of End(x"L"up) that's where I was wrong

[@kevin229168]

+Rohan K - Thank you, couldn't figure out where I had went wrong :)

[@RohanK]

+KevinHall Yeah I can understand hard to differentiate between l and 1 in the editor :p

[@Michael1ndonli]

+Rohan K Lifesaver!!!

[@MohammadRayeesAhmad]

For i=2 lastrow if cells (i,1) 《》 "anyvalue"then cells(lastrow,1)="anyvalue" next but it is not working plz help thanks frnd

[@manirulkhan8778]

Great Video, Great Explanation. I did it in Sheet1 & Sheet2 successfully. Only one problem. Codes must be run from Sheet1. Any Help??? Sub finddata() Dim athletename As String Dim finalrow As Integer Dim i As Integer 'row counter Sheets("Sheet2").Range("A2:E10").ClearContents athletename = Sheets("Sheet2").Range("G2").Value finalrow = Sheets("Sheet1").Range("A50").End(xlUp).Row For i = 2 To finalrow If Cells(i, 1) = athletename Then Range(Cells(i, 1), Cells(i, 5)).Copy Sheets("Sheet2").Range("A50").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats End If Next i Sheet2.Activate Range("G2").Select Application.CutCopyMode = False End Sub

[@cameraAndCup]

Great video, thank you!

[@peppenicoletti3433]

hello, i get : "error 9" subscript out of range could you help me?? plsssss

[@shashikalagangappa4735]

Thank you sir it is very useful

[@Dopeboyz789]

How do you write a code matching any rows or columns

[@JohnLythe]

I am not sure of your question but this macro loops through each row to find those that match the criteria. Video 78 searches for multiple names.

[@Dopeboyz789]

@@JohnLythe I found an example. kzbin.info/www/bejne/l526nHyLd8Z3oLM

[@alcauteraggie0440]

What if we are copying and pasting between two worksheets?

[@JohnLythe]

Video 78 on the same channel covers that

[@Josh-ge7hw]

amazing help!! great video!!! :) thankyou!

[@mohanokorsovann2546]

Hello may I request for excel version EAF#37

[@aurobindomohan7134]

how to do this search for multiple sheets??

[@tannertucker22]

Thank you. Excellent.

[@akahn8311]

If I want the result not in the Data Sheet but in a Text Field of a Userform how can I do this ??? Please can you help. Thank you for your time n help

[@edtardaguila3599]

the for loop is not adviseable if you have 20000 rows in your excel sheet this will gonna slow the the execution of your code this is why I am no using this anymore better yet try: Do loop with find and find next combination look up

[@Ingrams]

awesome! thank you sir

[@Enrique2k52]

Brilliant!!!, Thank you.