How To Automate VLOOKUP With Excel VBA
45 هزار بار بازدید -
3 سال پیش
-
Have you ever wanted to
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
https://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
https://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 (VBA) for Beginner...
📺EXCEL VBA BEGINNER COURSE (1 HOUR)
How To Write Your First Excel VBA Mac...
📺EXCEL VBA BEGINNER COURSE (15 HOURS)
(1/30) Excel VBA Absolute Beginner Co...
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
https://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 YouTube comments.
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
https://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
https://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 (VBA) for Beginner...
📺EXCEL VBA BEGINNER COURSE (1 HOUR)
How To Write Your First Excel VBA Mac...
📺EXCEL VBA BEGINNER COURSE (15 HOURS)
(1/30) Excel VBA Absolute Beginner Co...
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
https://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 YouTube comments.
3 سال پیش
در تاریخ 1400/09/12 منتشر شده
است.
45,027
بـار بازدید شده