How To Automate VLOOKUP With Excel VBA
HTML-код
- Опубликовано: 2 дек 2021
- Have you ever wanted to do this in Excel?
You’d like to ‘look up’ an entry from a table. In the video, we use the example of the Doggy Football League and looking up home grounds from team names. Well, that’s easy with VLOOKUP, right? But what about this common additional requirement: usually you’d like to look up the entry, but occasionally you’d like to ‘overtype’ or enter something different. A kind of dual function. Is this even possible? This poses a real problem for Excel users because, once a formula is ‘overtyped’, it doesn’t come back unless re-entered manually. So, what to do?
🔥FREE: YOUR EXCEL CHEATSHEET MINI-COURSE
tinyurl.com/jxezd355
Excel VBA offers us an elegant solution. It’s hardly beginner level, however, because some complex syntax is needed to get everything working. As I show in the video, we can make things easier for ourselves and, of course, the Excel download file is available with a fully working example. Follow along with me and see if you can get it working too - it could unlock a new world of functionality in your data analysis work.
💼EXCEL DOWNLOAD FILE
tinyurl.com/27wjacnv
It’s a little-known fact that Excel formulae such as VLOOKUP can be accessed from the VBA editor. We’re not talking about native functions in Excel VBA such as RIGHT and VAL. Rather, Application.WorksheetFunction tells Excel to find the worksheet function and harness it in Excel VBA. It gives us the power of a worksheet formula in VBA. In this video, we explore an application of VLOOKUP - but I also frequently use COUNTIF and MATCH with Application.WorksheetFunction.
📺EXCEL VBA BEGINNER COURSE (20 MINS)
• Excel Visual Basic (VB...
📺EXCEL VBA BEGINNER COURSE (1 HOUR)
• How To Write Your Firs...
📺EXCEL VBA BEGINNER COURSE (15 HOURS)
• (1/30) Excel VBA Absol...
Should we use it all the time? No, most formulae are best as they are - formulae in the worksheet. From time to time, however, Application.WorksheetFunction solves an Excel headache, as it does in this case. It’s also a great coding challenge that will stretch and enhance your understanding of Excel VBA.
The real benefit in this case is that the ‘looked up’ value (the name of the home ground) is entered to the spreadsheet as a value, not a formula. Suddenly, all our problems are solved! At the click of a button, we can pull in the values AND afford the user the option to ‘overtype’ the entry if they need to …
🔥FREE: YOUR EXCEL CHEATSHEET MINI-COURSE
tinyurl.com/jxezd355
Putting together Application.WorksheetFunction requires patience and awareness of a few things that make the process (slightly) easier. I recommend building the formula in the worksheet FIRST, then, as I show in the video, use the inverted comma to store the formula as text in a visible cell. You can reference this from the VBA editor. It’s important because the prompts VBA provides are not as helpful what you might be used to - as we discover in the video.
So, did you get Application.WorksheetFunction working? What applications can you see for Application.WorksheetFunction in your work? And would you like to see more videos about this topic, perhaps with other formulae? Let me know in the RUclips comments. 
Развлечения
![Skilla Baby - Misfit (Feat. Polo G) [Official Video]](http://i.ytimg.com/vi/IjsylSnNMyw/mqdefault.jpg)
🔥WATCH NEXT
🔥EXCEL VBA ARRAYS FOR BEGINNERS
ruclips.net/video/XH2jZ7a7THM/видео.html
I will have to thank you massively, Sir. Not only was that a great tutorial from a coding standpoint, but I also enjoyed your communication and video-style, both adding to one great educational experience. Keep up the good work, I think VBA will still remain relevant for years to come since I see tons of it in my work in the banking industry on a regular basis, and businesses will want to take advantage of the Database-enhancements/queries in Excel, which can be further advanced through VBA and so forth.
Hi Patrick - thank you for the lovely comment and I couldn't agree more with your prediction about the future of VBA. All the best and see you in another video!
Thanks for your efforts in simplifying the information
It’s a pleasure and welcome to the channel
superb video and so easy to understand
Cheers, Lee. This one is well worth knowing I think.
Totally brilliant
Thank you, my friend!
A new and interested subscriber from Algeria 🤗 Thank you and keep it up 👌💪
A big welcome to the channel! I hope the videos help ...
For the variable chris cell, is there a way to select a range depending on the size and not a fixed range ("E6:E15")? Like for example, using vlookup in a weekly report that is different every time
Hey, did you find a solution to your question? I'm trying to figure out the same thing.
Absolutely - but you'd need to dynamically define the range. Check out our video 'Position Control Mastery In Excel VBA In 13 One-Line Macros' - good luck!
@@TigerSpreadsheetSolutions Genius. So you need to Define a range. This code will save me so much time! Thanks
Do you have a video in which you explain how to create multiple buttons which will record the values on a column when pressed any of it?
Andrei - check out our videos from 2016 / 2017. Around then I did lots of tutorials on buttons ...
@@TigerSpreadsheetSolutions Sure, I'll check now. Your videos are Gold!
If I have the List sheet in a different excel, can you please help me what to use instead of the Sheets("List") part?
Hi there - you'd need to add the workbook reference at the beginning something like:
Workbooks("File Name").Sheets("List").range("A1:B5")
... and ensure the other file is open
مرسی
Great Video, but can I do lookup to another workbook? not in the same file
Alvin - this is not something I recommend, but is possible. You have to include the reference to the workbook in the VBA code. We have the information on our channel, or a Google search will help - good luck.
hi chris
this vlookup vba works for the sheet to sheet or it will work for one work book to another workbook
my priority of vlookup is from one workbook to another workbook
i have tried the code and run the F8 i am getting
run time error 1004
can we help me out
Hi there - 1004 is usually caused by a syntax error related to a sheet name or a file name. You might or might not need the file extension (eg. .xlsx) in the code. Check spelling of all sheet names and range names in the code. Good luck!
Hello sir ,
Where I reach vba code to reference the editor doesn't give me the sheets referencing as you demonstrated
Hamza - all I can recommend is to work through the video again very carefully, paying attention to spelling and syntax ...
Hi How do I continue with next cell. I want to lookup more than 1 cell. Thank you
You would need some more complex VBA I imagine - there are many resources to learn on this channel, good luck!
u need just to use a range from first cell to last cell , to do thats u have to calculate the count of yours rows at the columns thats have ur lookup value ( for exemple column 1 )
1-declare a type long
2- nb=worksheets("1").cells(rows.count,1).end(xlup).row ( this commande count the number of rows in columns 1
3-now you can use nb in range like ; sheets ("1").range ( sheets.cells(1,3),sheets.cells(nb,3) , (number 3 the columns where u want to put ur function vlookup
Can someone explain what he's explaining at 3:01, regarding the last two parameters of the Vlookup function? He's speaking so fast I can't interpret what he's explaining despite listening 5 times in a row.
Hi there - use 0 for the 4th part of the VLOOKUP formula when dealing with text, also known as 'discrete data'.
Use 1 here when working with continuous numerical data.
I do plan to do a video on this sometime - thanks for the comment!
The video is great but there is something about the sound.
Yes, I think it's a touch over-processed. I will dial it back a bit next time - thanks for the feedback.
Bro u speak like Tyson Fury) good video, just please fix the voice and keep up)
Thank you, my friend! You're not the first to make this comparison :-)
The tutorial was easy to understand but I get error 1004 😅
Hi Jenny - check any sheet and range names that are involved in your code. Spelling mistakes can trigger this error. Good luck!