I am on video 8/10 and don't wont it to ever end, your amazing at explaining VBA and I go to a supposedly great university and they can teach like you. Thank you
Nicolas - don't worry, there about 100 other videos for you to watch after this series :-) Thank you very much for the kind comment, and hope you continue to enjoy the content.
Hi Chris, I'm following along with your series and I ran into a problem with this part of the code: (ref: Series 8/10) Dim TargetRow As Integer TargetRow = Application.WorksheetFunction.Match(Column_A, Sheets("Data").Range("Dyn_RMANumber"), 0) MsgBox TargetRow It runs perfectly fine for a couple of trial runs and then error 1004 comes up, highlighting the TargetRow code. Thanks for putting out these series, very informative!!! Ray P.
Having the same issue here. I brought the code from the final file over to compare and can't seem to find any differences. The combo works fine and the dynamic range worked fine on the "Select" exercise.
Hi Jack Possible problems: 1. Just to check - is Column_A a single cell, or value? It has to be for the formula to work. 2. When the code is executed, is the Data sheet the active sheet? This might be problematic. 3. Is the value to matched (Column_A) definitely present in the list you are matching to (Dyn_RMANumber)? Check the dimensions of the dynamic list by selecting it. Worksheet formulae in VBA can be difficult to debug. I suggest recreating the formula in the worksheet to see if you can identify the problem. Good luck!
Thank you for the quick reply, Tiger. Btw, these videos are excellent. Here's my code: Dim TargetRow As Integer TargetRow = Application.WorksheetFunction.Match(Acct_Sel_Combo, Sheets("Metadata").Range("Dynamic_Accounts"), 0) MsgBox TargetRow Acct_Sel_Combo points to a single value in the drop-down menu and Metadata is the active sheet. I recreated the match command in the worksheet (substituting Acct_Sel_Combo for the actual cell where the value is stored) and it correctly pointed to the correct row number within Dynamic_Accounts. I also tried dropping the "WorksheetFunction" but this time got run-time error 13. Thank you for the support!
For some reason was having a hard time getting the text to show up properly in the form buttons. Replaced them with Shapes. Much more control over appearance and can still assign macros.
That's true Stephen - but I find people intuitively 'click' buttons, shapes don't quite have the same effect. Which is why I keep it simple and stick to buttons!
@@TigerSpreadsheetSolutions The shape is button shaped with appropriate text...looks just like a form button but a lot easier to work with, at least for me.
Hi Chris! I am having an issue when i click the continue button on the search UF i get this error: 1004 ERROR "Unable to get the Match property of the worksheetfunction class". I have discerned that it is because the data in the column i have made is numbers rather than text and i wanted to know if you have a solution to this? Is it possible to have the combobox update when you make a selection so that the selection is treated as a number rather than text? i have tried formatting the numbers in the column i am referencing to the combobox as text as well but that didnt seem to work. Really appreciate any help! EDIT: moments after posting this question i found a solution. Because my column contained numbers i had to convert the value in the combobox to a double so that the data types would be the same: Option Explicit Private Sub Continue_Click() Dim TargetRow As Double TargetRow = Application.WorksheetFunction.Match(CDbl(ComboBox), Sheets("Data").Range("Dyn_Range"), 0) MsgBox TargetRow End Sub
I receive the same error message, 1004 ERROR "Unable to get the Match property of the worksheetfunction class". I then tried itismicah's code suggestion and it works but, then I get an error when I try to edit a newly created entry. The edit works on all the existing entries but won't with a newly created one. Any suggestions?
Scrolling & finding name in combo box is sometime very unproductive, can name appear as you start typing the alphabets so that we can have exact match to select.
OMG it took me 2 hours to figure something out. So I use German as system language but prefer to write vba code in english and never had a problem since the fx&code work perfectly fine in both languages... Now I wanted to use the offset fx in the names manager just as you showed... but nothing... Excel denied all tries ...until I tried the German =Bereich.Verschieben()... So obviously the names manager is the only place in excel where this happens...
Hey Chris, Thanks for the video's Yesterday everything was working fine, now: Running into a 1004 ERROR "Unable to get the Match property of the worksheetfunction class" when trying to retrieve the UF in the Edit process. Checked everything multiple times, looked for work arounds... Help? Thanks
Rolf - looks like a problem with the match function used in application.worksheet function. Why is the match function not working? This is usually because the value Excel is trying to 'match' is not present in the table. What value is Excel trying to match? What values are in the table? Look out for values stored as text, Excel may not match these. Compare your file to the completed download file where the mechanism is working. Good luck, Chris
Excellent video series. I will be watching more of your stuff. I just have one issue that everyone else seems to be having but mine is a little nuanced. The Match function works when there are letters in the column with numbers. but If i have just numbers, it does not work and i get the runtime error. I am creating an inventory database and i am using the partnumber column as the search area for the match function. how can i get the match function to work with different style part numbers ie: A123BC and work with just 12345 also? is this even possible?
Tiger Spreadsheet Solutions - I have another concept to work through and I’m not exactly sure how to go about it. I’m going through your videos and trying to figure it out
Hi Chris, I know it's been a while since you posted this but it's worth a shot. I'm on the part where we're creating the dynamic range using the name editor. When I insert the Dyn_Name onto the row source I get a prompt telling me "Could not set the RowSource property. Invalid property value". I've tried using both the cell value e.g. $E£8 and the table value e.g. [Table4][@FullName] and neither seems to be working. Is this because my data is in a table and can't be named properly? If it's case of undoing the table, how do I create the dynamic ref numbers you have on column B? EDIT: I've fixed the first problem, but I am now getting an "Unable to get match property of worksheet function" message. I'm sure I'll figure it out eventually. Also thank you for the excellent tutortial, especially explaining the variables and syntax.
Good to hear you solved this Mathew, I think! Application.worksheetfunction.match is a tricky one. I recommend practising with the MATCH formula in the worksheet first.
I did figure it out eventually. I just went over some of the syntax you explained in an earlier video and figured it out. Your explanations of each VBA variable and how they interact has been incredibly useful. I'll be sure to watch your videos over the coming weeks
Halo Tiger! i have this kinda problem, Private Sub CommandButton1_Click() Dim TargetRow As Integer TargetRow = Application.WorksheetFunction.Match(ColumnG_Menu, Sheets("Data").Range("Dyn_Student_No"), 0) MsgBox TargetRow "ColumnG_Menu" is the combo with dynamic range "Dyn_Student_No" is the dynamic range it does not go through. can you please help!
Hi, I have enjoyed your videos on VBA. I want to add a macro or function to this project I am doing. After I add a new contact I want to produce a Delivery Label. I want to select data from which I have entered and populate a template xls with our company logo of which i want to deliver a sample pack and welcome them. The delivery not will be used by our warehouse team to attach to the pack.?????
Hi Joe - good to hear about your project. Why not create the template on a separate sheet, including the company logo there? Then, you should be able to create a mechanism to pick a record from the database and to transfer data from this entry into the template. Good luck!
I appreciate your reply. Also, I wanted to mention that as a beginner I have learned more from your videos over a few days than what I have learned from my colleges over the last 15 years. my confidence with VBA & excel is growing with your help many thanks. can you give me some guidance or refer me to a video that can help me set up an appropriate task/ mech to complete my request? regards Joe Perth Australia
Joe - great to hear about your progress. Confidence is crucial and once you feel you can experiment with different ideas in VBA, the sky is the limit. Please do tell others about the channel and I hope you'll keep coming back here through your career. Good luck!
HI, I must admit I am struggling to create a mechanism to produce an output to populate an external template. I have created a template in excel in a different workbook. I have got it to look perfect for us. I have cells on the template where I want the information to populate. I want to add an additional control button to my user for which will save and print the entry. I know I need to start with creating variables, then find last row with data, copy selected data to specific cells, then show then print the form as read-only, clear the template for future use, automatically save a copy of the printed document in another folder for record keeping and name the file with ref# date & customer name. just looking for guide?
Hi Joe I would position the template in the same file as the database. The logic of the code: - allow user to choose somebody from database (dropdown menu?) - clear existing data from form - locate the target person in the database (which row) - using this row, transfer data from database to form We have video series called 'Excel VBA for Post-Beginners' where I do exactly this. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Y-8pP_GWnao.html Good luck.
my database contains numbers with green triangle "the number is formatted as text or contains apostrophe", which it doesn't contain either. I formatted the column as a number but that doesn't solve the issue. More importantly the sub routine for the "continue" button has run-time error "application-defined or object-defined error". I've gone over the code several times and I can't figure it out. Any suggestions?
Hi Mike - I can only suggest going over the code very carefully, using the 'step into' function in the VBA editor, to try to understanding where the problem is. With regard to converting text to numbers, the =value formula might help. Good luck!
Tiger Spreadsheet Solutions Thanks. I found the problem, there was a misspelled word in the code. BTW, really great videos to learn by. Thanks for helping me out.
Hi, I cannot seem to get the formula, =offset(Data!$E$8,0,0,Engine!$B$3,1), that you called Dyn_Full_Name in the name manager. Any thought on what the problem can be? Thanks.
Hi Chris, thank you very much for this tutorials. They are very well done. One question, when I run your file ( testing TargetRow ) everything is fine. On the contrary when I try mine, which I'm building while looking at the video, I always get this error:" Impossible to find match for the worksheetfunction class". the problem is that if I switch ColumnE_menu with a fixed number or name ( in my case I'm using fixed numbers) everything worksfine as follows TargetRow = Application.WorksheetFunction.Match([10 or "julia hernandez"] , Sheets(1).Range("Dyn_Full_Name"), 0) , but in this case i'm not connecting the combobox ( ColumnE_menu), when I connect it .....boom everything stops working....I hope the problem is clear.....I really Don't know what to do....
My apologies, it's 2 hours that i'm trying to fix the bug, almost to the point of throwing the PC of the window) but I think I fixed it. I shall explain if anyone gets the same error. ---------------------------------- Private Sub ColumnE_Menu_Change() Sheets("Engine").Range("b7").Value = ColumnE_Menu.Value End Sub ----------------------------------- Private Sub CommandButton1_Click() Dim TargetRow As Integer TargetRow = Application.WorksheetFunction.Match(Sheets("Engine").Range("b7").Value, Sheets(1).Range("Dyn_Full_Name"), 0) MsgBox TargetRow End Sub ------------------------------------ basically I'm having the combo box to print the value selected on the engine sheet and then I pick from there for the TargetRow function so far so it's working :) now I can move to chapter 9
for the editing option can we just use unique function to get the data for the dropdown menu , but i don't know how to do it using the method worksheetfunction
In my case the solution was annoyingly simple. My excel language is hungarian, not english, but we're coding VBA in english. In the ecxel sheets OFFSET funcion have a "hungarian name", another is not working ! .the most terrible that - previous - in COUNTA function I think about this option, but here I spend half a day.....not problem..... Thankx the video. You're the best
If there were multiple entries of the Full Name in this database, how would you manipulate the VBA to search for the very last occurrence? So, if there was another entry for Julia Hernandez on line 16, how could the program show 16, instead of 1 in the MsgBox?
Hi Dennis - interesting question. One approach would be to timestamp each entry, then to sort the data by date at the beginning of a macro. You could then get Excel to loop through the data and exit the loop on the first occurrence. Just an idea for you - good luck!
Hey Chris, I was wondering if you could help me out. Everthing works until, I add the "Dyn_Full_Name" I receive and Run time error 1004 . Aplication-defined error. So I look at the debug and this is what the debug is highlighting "Sheets("CData").Range("Data_Start").Offset(TargetRow, 3).Value = Txt_Fname" Can you help. Thanks Kellie
Awesome video! I'm trying to learn VBA and these have been fantastic. I'm trying to create a spreadsheet that houses contact information for all departments within a business. The number of rows within each department vary from 15 to 600. I'd like to structure the spreadsheet in a way that allows the user to either; select the department from a drop down menu and all of the employees within that department appear or, have the user click an applicable button and the employees within that department appear. I'd also like to apply the userform structure you integrated as it ensures accuracy and utilizes preventative measures. Do you have any suggestions!? Thanks again!
Excellent Video's. I can't imagine why anyone would give you a thumbs down. Keep up the excellent work. I've been programming in excel for over 5 years now as part of my job responsibilities. Only in the last year has constituted more than 70% of my work. I like how many variations there are to perform the same task. That is why I watch a lot of videos. Yours is one of the easiest and clearest training videos I've seen so far. Thank you for your commitment to good, free, hands on training.
It's a pleasure and welcome to the channel. Hey, somebody will always give a thumbs down - I'm happy for the engagement. Let me know what other videos you would like to see on the channel.
Thank you very much for this series it is really helpful!! I am running into a problem, unlike your sheet I have first and last name in different columns and know there will be multiple entries with the same name but different people. I want to add a 2nd verification to check the zip code as well which I am able to do. However, I am stuck on how to be able to display this in the combo box as these are in 3 different columns in the sheet. For eg. I want the combo box to show something like "bob smith 12345" with the data being pulled from 3 columns. Thanks very much!
Hi Bruce - you're welcome and good question - this is something I have dealt with in the past. There is a way to get the combo box to display multiple columns - try putting that into Google. Alternatively, you can create a new list in the spreadsheet (using VBA) which consists of name + another piece of information, then use this list to power the combo box - this approach I have used recently to good effect. Good luck!
Amazing videos! I have followed this and made a great user friendly form. But I have one problem, when I use the edit button it will not run the code if the data has been pasted into the cell. In context to the video, if I were to paste the full name and then look up using the edit function it would just tell me to end of debug, but if I type it in manually it runs the code…
Hey man a request for you. Unless I missed it (I have to finish the last two vids one more time and maybe it is there) how about upgrading this 10 part series with an 11th bonus video on how we could add an 'Auto-Alphabetize' button that would take all the data entry rows and alphabetize them upon clicking it. Making the button is easy. And I have something I am working on right now that might work. But it's going to take some time combining the proper codes and getting them to work with what I've learned from you in these series. I know you have other things you want to do but your tutorials are among the best and my favs and I really think something like this would compliment and go hand in hand with this 10 part series :D
That's an interesting idea. I think you are talking about automatically sorting the data in the worksheet when a new entry is made? I will certainly consider this for my next set of videos. Thanks for the support Mr Jackson!
First and foremost: THANK YOU for the amazing content. Thanks to your guidance I have been able to create a nice UserForm at work that will be used within the department and has been received enthusiastically. I did need to tweak the code here and there to make it fit for usage in the office. Now, in some textboxes the users need to input a budget number (in EUR). However, after clicking the "continue" button the output in the excel database displays the number but I do not get it formatted in EUR. It is just displayed as a number and excel does not even recognise it as a number but just as text. Can you please tell me what I should do to make this work properly?
Good to hear Bram and well done on the project. Use the VBA syntax =VAL when transferring values to the spreadsheet to ensure Excel understands them as values.
Thanks for the video. Like a lot of people, I think, I was getting the 1004 error, no idea why or if it is because i was trying to search for numbers. I fixed it by adding another integer and equalling that integer to the selection box result and using that in the match function Dim CB As Integer CB = CbPlotSelect TargetRow = Application.WorksheetFunction.Match(CB, wsp.Range("RANGE_OCC_PLOTS"), 0) #wsp is my worksheet call in short be nice I'm new to vba so I don't know if this is a Band-Aid or a real fix.
Thanks Tim - I recommend if struggling with application.worksheetfunction that you try to build the formula in the worksheet first, before transferring to VBA
@@TigerSpreadsheetSolutions The first thing I always do is take it apart to see what doesn't work and rebuild with just numbers then introduce formulas and ranges until it breaks. Then test all the avenues I know of to try to get it to work. Which is where I struggle with VBA cause there are no prompts. Getting there tho! Your videos have helped loads
Hello, thx for the invigorating video's. I have an error 1004 running your test on the dynamic range that I can't pinpoint. my range: =offset(Koerier!$B$4;0;0;Hulp!$B$5;1) Any idea's?
@@bartschoofs4160 Yes Bart that's sometimes a problem - if you have your Excel set to a language other than English you will probably need ; rather than ,
Hello Mr. VBA, 1st let me tell you that your videos are really good will help people create their own way, because of your step by step instruction with an explanation, what are the use of each and every code you draft Need your advice, pls find below is my coding for the search box ------------ TargetRow = Application.WorksheetFunction.Match(ColumnD_Menu, Sheets("DATASorce").Range("Dyn_IRIDNo"), 0) Sheets("Engine").Range("B4").Value = TargetRow ---------- Getting this error when I key in wrong data (manually enter data which is not in the list) in the combo box and click on "Search / Find" command button Error : (Run-time error '1004') (Unable to get the Match property of the WorksheetFunction class) Can you pls share me any workaround codes so that I get a msgbox that the data entered is not matching the database and reload the "userform" Expecting for your revert Your the best.
Hi Nanda - this is definitely a problem with the match formula. Most likely, the match formula cannot find the value you are looking for. I recommend setting up the formula in the spreadsheet first where the errors are easier to understand. Once it is working in the spreadsheet, create the application.worksheetfunction code in the VBA editor, based on what you know is working in the spreadsheet. Good luck.
Hi Chris When I copy and paste Private Sub CommandButton1_Click() End Sub,for example, it doesn't insert a new line with the code underneath so I can edit it all it does is show the original message, yet it seems to work okay as the user forms I have created on the spreadsheet appear and I can enter new names on to the database. So if I make a mistake I can't find the code to correct it
Enjoying the video series. Getting this error "Run-time error '1004': Unable to get the Match property of the worksheetFunction CLass" with this code: Private Sub EditButton_Click() Dim TargetRow As Integer TargetRow = Application.WorksheetFunction.Match(EditRow, Sheets("Modern").Range("Edit"), 0) MsgBox TargetRow End Sub Any assistance would be appreciated.
Hi there I could only recommend working through the components of the problematic line of code, one by one. - what is the value of the EditRow variable? - what is the "Edit" range? - are any values saved as text? Match may not pick them up. Also compare to the completed download file. Good luck!
Hi Chris, I really enjoy your videos, they are informative and generally easy to understand. In watching 8/10 I am stuck on the edit button. It seems when I try to enter the information in the Rowsource. I keep getting the error message "Could not set RowSource property. Invalid property value. The worksheet I am referring to is titled 2019-2020 and the rows are d2:d250. I am typing in 2019-2020!d2:d250. I suspect this is a simple issue or that I am missing something. I have tried many different combos and can't seem to figure it out. I was hoping you may have an answer for me. Thanks
Hi there - glad the videos are helping. Have you tried using inverted commas around the sheet name? eg. '2019-2020'!d2:d250 - that should work. Let me know how you get on.
Hello Chris, yours is the easiest tutorial I've seen, excellent work, thank you very much! I'm doing my very first VBA project. I'm on video 8/10 and got stuck at retrieving data for editing, because instead of the names I used the number (auto generated, as unique reference serial numbers for each row entry) because this is the only data that cannot/must not be duplicated. I believe this is my last hurdle, your help is most appreciated. Looking forward to hear from you.
Hi Ignacio - thank you and welcome to the channel. Look through the comments for this series for some advice on how to do this - it's not straightforward but is possible. I have build solutions that concatenate both the first and second name and a row number in a single cell; then I use a this range of cells to drive the combobox. Then you can separate out the value from the user selection to identify the row you need. As I said, it's not simple but is possible - good luck.
I have used you ideas on building a form to input my data into a data sheet. You videos/tutorials were so good without any previous experience with VB I managed to build my own. I am having trouble with dates on my form, I enter UK dates but VB then uses it in USA format. I have to add 3,6, or 12 months to my UK date I enter for a start date on the form which then show a return by date in another text box but when it appears in my data sheet it has added the 3 months the the UK day. Do you have any suggestions with handing dates in VB.
Great videos. Thank you so much. Everything was working great. However, I think I made a mistake, that I do not know how to fix. I formated my data into a table yesterday before moving to the next video and now when I hit the continue button to enter the data, excel crashes. Thoughts?
Hi Chris, I am having trouble trying to get the command button to work while following your video. It works no problem when I follow your instructions but I am wanting to use Column A in my combobox instead of column E as in the video. It will select all my data in my table under column A and will display the correct values in my combo box but when I hit the command button I get a runtime error.
Hi Andrew, very difficult to say without viewing the file. I would check all of the sheet / cell references very carefully. Try to identify what the routine is trying to do when it crashes. Good luck!
I was able to figure it out by changing my data set into a table and using the text(row) function to populate the rows of my table with the row number. I then set my Dyn_Entry_Number to use this row. Your videos are great btw and really got me into using VBA keep up the good work!
Hi Tiger, I did everything exactly as the video, but when I add the source row to the combobox I receive this error "Could not set the source row property. Invalid property value", any ideas how to solve this please? thanks
Hi Walid - make sure you've included an equals (=) sign before the named range - assuming you are using a named range, there may be a problem with that. Check that it is working properly as a dynamic named range first. Check out our dropdown menus series for a detailed explanation of how to create dynamic named ranges Good luck! Chris
Hi Tiger, found the problem, my excel is in french, I used the french equivalent of the offset function and it worked. Great videos by the way thank you so much :)
I'm using vbNewLine to do two entry in one cell, how do i make a targetrow variable execute those two cell entries? i tried writing a code but i keep getting 'run-time error 1004'
Hi there - review tab / protect sheet, then use cell formatting settings (right click on cell / format cells) to ensure the correct cells are protected and not protected. Good luck!
Hi Tiger, I've been following your videos. Everything works fine except when I try to make the dynamic range. =offset(Data!$D$6,0,0,Engine!$B$3,1) this is my formula for the name modifier. My Data starts at D6. I've named it Dyn_CIP since it's more representative of what I do. This is my formula that I use in module 1. Sub TestDyn() Sheets("Data").Range("Dyn_CIP").Select End Sub When I run the formula I get a message box : Error 1004. Application-defined or object-defined error. I cannot seem to find a way to make it work. Thanks for the feedback!
Samuel All you can do is check each element very carefully: - is the sheet name accurate? - is the named range accurate? - is the Engine cell reference accurate? - does the Engine cell contain a value? etc. Good luck, Chris
Hi Chris Thanks for your series! Gave me a lot of insight into the userform subject. I have a problem with editing my data. I would like to edit a post in the data base. I would like to edit by ref number. is this possible? It works fine by searching on the name - but not by ref number. Can you help me? Best regards Martin
Martin - good to hear you found the series useful. Yes, it should be possible to search by ref number. You would need a combobox first (with a dynamic source) to select a number, then use the application.worksheetfunction.match mechanism to identify the row that contains the ref number. Then do the edits in that row. Good luck!
@@TigerSpreadsheetSolutions Hi again - when I run the TargetRow sub it gives me this error message: Run-Time error '1004: Unable to get the Match property of the WorksheetFunktion class - Du you know the problem?
Martin - check that the value you are looking for using MATCH appears in the target range. If numbers are stored as text, Excel may not 'match' them, so make sure numbers are stored as values. Also check the references of the (dynamic) range that the match function is looking at. See if you can get it working in the worksheet using a normal worksheet formula, before trying the VBA function - good luck.
