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!!!
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.
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.
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...
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.
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
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
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
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!
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.
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
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...
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
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) ?
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.
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!
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?
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
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.
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.
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?
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
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
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.
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.
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?
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!
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?
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
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
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.
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?
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
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 !
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.
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)
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
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 )
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
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
take a look at this - ua-cam.com/video/A23S1ybHjJg/v-deo.html 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
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
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 ?
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.
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
I love UA-cam for learning everyone's VBA design perspectives. I'm always looking at code in unlimited ways.
After watching other videos and still feeling confused, you explained it so much clearly. Thanks!
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!!!
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.
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.
It's an amazing video. I'm learning VBA and fill happy when good people spread knowledge. thanks
Hello ExcelTricksforSports,
Your video is excellent. After hours of anguish I have finally found a code that works for mini-project.
Grazie mille!
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...
Your keyboard honestly sounds amazing oh my word
This was very helpful. It helped a lot! Thank you for such a detailed description and making it so easy to follow.
I just stumbled onto this tutorial and it is perfect for a project I am working on! Thanks so much!
THANKS A LOT U GAVE ME A NEW IDEA, SALUDOS DESDE SANTIAGO DE CHILE.
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.
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
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
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
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!
Thanks for the video. It's very insightful. How do I return those data to text boxes in VBA userform?
Thank you so very much it was very helpful!🤍🤍
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.
Thank you! Really helpful for VBA beginners
thank you very much. this video is really helpful. it's really help me a lot.
Excellent solution and explanation, exactly what I need! Thanks a million for your help, you get a new subscriber 😀!
God bless you!!! Yoo help so so so much!! Thank you!
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
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...
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
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) ?
Thank you so much for sharing these useful skills!
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.
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!
immensly useful , going to walk through your videos...cheers!
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?
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
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?
Did you use Data validaton in Athlete names?
How did u extract unique names ?
Pls reply
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.
amazing help!! great video!!! :) thankyou!
nice video. hello can i know how you extract all the unique names in column N from column A using Vba?. thanks
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.
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?
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?
Hello, I want to know, what if the database is at sheet 2, and I want to display the data at sheet1 ?
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
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
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.
Oh okay, nvm I see now. The clear content clears it after the procedure has ended. Totally missed that.
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.
hi, if i want to add another drop down to search another information example by specific data, how would the code be?
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?
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!
Yes this kind of copying and pasting works great with VBA
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?
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
Thanks so much! Simple, easy to understand and easily adaptive code! Good stuff :)
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
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.
Excellent video my gust ion max rows. 30 000. When I have more rows what to do
Thanks for you help. ton
Hi, I need your help..in time sheet data how to indefine how not filed time sheet and get report
Thanks for sharing , is very useful
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?
Great video, thank you!
This was very helpful, but am I wrong in thinking that what we've done here is just make a manual pivot table?
Done the similar work.. but code is not working at all..!! Did you use Data validation for drop down of athlete name?
Can you please help me how to convert the code on ms access using excel as data base?
Thank you sir it is very useful
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
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 !
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.
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?
Sir
Excellent video
My search Dim is a Date, an integer,
Will the code work?
Or it is for String?
Regards
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.
A quick question sir, how did you do the "count" (Q2) and sort the "name" (P2)?
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)
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
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
Also how do you match it with numbers instead of letters
I need both string and integer as a find data how give dim code
What if it p2 ,q2 and r2. How do you write it in vba?
Brilliant!!!, Thank you.
Thank you. Excellent.
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 )
can i obtain the worksheet in excel so that i can follow your tutorial
Thank you For Your Tuts
Find Records with Start Date and End Date Can you Explain
Could you use this same macro but search by the dates in the 2nd column instead of the names?
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
ExcelTricksforSports
Sir
Date does not work
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
ua-cam.com/video/-QFjJoRGCtU/v-deo.html
Video 78 shows this
Hi, this works fine for me, but now i need to match more than one cell, how can i do that?
awesome! thank you sir
How do u use it in a formula instead of vba
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
Dear ExcelTricksforSports,
If I wanna match more than one cell, what should I do? (I mean multiple criteria)
take a look at this - ua-cam.com/video/A23S1ybHjJg/v-deo.html
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
Hi
How can I do the same but this time scanning ITEM and finding record in my database.
Thank you
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.
Hello may I request for excel version EAF#37
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?
Please reply on pandeysfs@gmail.com thanks
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
you should use Cells(Rows.Count,1).end(xlup).row for the last row :) that is the better you u should learn people :)
How make it work in other sheet?
Check out video 78, there are 5 examples that might be useful ua-cam.com/video/HyOmcbHIi0w/v-deo.html
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 ?
how to do this search for multiple sheets??
Thanks. Quite useful code.
Is there something wrong with the codes? its showing variable not defined. (x1Up)
Anton France Munoz the code looks slightly wrong. (xlup) = XLUP not it is L not 1(one)
hello,
i get :
"error 9" subscript out of range
could you help me?? plsssss
How do you write a code matching any rows or columns
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.
@@JohnLythe I found an example. ua-cam.com/video/alWkIUBaDjQ/v-deo.html
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.
I just realized i made a mistake, *john not joe
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
ExcelTricksforSports Thanks, I will give it a shot.
What if we are copying and pasting between two worksheets?
Video 78 on the same channel covers that
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
+Rohan K - Thank you, couldn't figure out where I had went wrong :)
+KevinHall Yeah I can understand hard to differentiate between l and 1 in the editor :p
+Rohan K Lifesaver!!!
Thank you!
Thanks very much, nice and tidy,
How to obtain the Count?