Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Transform VLOOKUP-formula in sheet to VBA

0

I am about to move some formulas from new added rows in a sheet to a VBA-script. This script updates a dashboard. In that script I have used several VLOOKUP's but the search area always has been either a single column or a hard programmed range that never changes.

I now need to refer to a search area being an Excel table at a different sheet and I am struggling a bit how to transform the formula into VBA.

 This is the Excel-formula (in Dutch)

=VERT.ZOEKEN([@Nr];Bestand_31122019;2;0)

 [@Nr] is of course the lookup-value in the current sheet, which is an Excel-table.

Bestand_31122019 is the name of the Excel-table in a different sheet.

 This should be the VBA-script

Range("CF2").FormulaR1C1 = "=VLOOKUP([@Nr], Bestand_31122019,2,0)"

Or with [ ] 

Range("CF2").FormulaR1C1 = "=VLOOKUP([@Nr], [Bestand_31122019],2,0)"

So the question is how to refer to the search area, being an Excel-table called Bestand_31122019.

Which one would be correct if any ?

excel
MS Office Version: 2013
posted by: eggem01 (rep: 2) on Thu Jun 11, 2020 at 4:27 am
Answer
Discuss

Answers

0

Here's the correct code.

ActiveSheet.Cells(2; "CF").Formula = "=VERT.ZOEKEN([@Nr],Bestand_31122019;2;FALSE)"
  1. When you create a table Excel automatically sets up a named range by the same name. In the above context it's the range name that's being used, not the table's.
  2. Since you are trying to work with variable cell addresses now you better get used to apply cell addressing syntax when you address cells instead of the syntax for addressing ranges. Note that the column in Cells(2, "CF") should be a number, too. It's Columns("CF").Column, which returns 84.
  3. Since you now start to address other sheets better accustom yourself to always include information about the sheet you are referring to in your code. If omitted, VBA will presume the ActiveDocument (=ActiveWorkbook.ActiveSheet). Much hair has been pulled while looking for something that wasn't there - the sheet's specification.
  4. There are no cell references in R1C1 format in your formula that Excel will have to translate correctly. Therefore the target cell's property to which the formula must be assigned is just plan Formula, not FormulaR1C1.
  5. The last parameter of VLOOKUP specifies whether the required match is approximate. If it's not, it must be exact. This is a matter of True or False. It's true that Excel will interpret a 0 as False. But it's not true that the purpose of the parameter is equally easy to recall when faced with 0 or 1.

Of course, all of the above doesn't answer the question why you use code to enter a formula that then does the actual work. Couldn't you just enter the result in the same time with the same effort?

posted by: Variatus (rep: 4889) on Fri Jun 12, 2020 at 3:21 am
Discuss

Discussion

Thanks for your quick reaction. The code is working now. I actually used your remark #3. Since I am not an professional developer I would like to respond to your remarks. I had to look why I am using the FormulaR1C1 in stead of just Formula. Turns out that I have used the FormulaR1C1 in other projects and just kept using this here too. Although not neccessary in this specific column it was in the other projects. So I agree with you on this matter. I use the vlookup frequently and I automatically type in a 0 in the formula for an exact match since I am always looking for an exact match. Your last remark perhaps requires a bit more background information regarding the script I am using. I have developed a large dashboard, with KPI and automatic email-reporting tool. This whole tool gets is data from an external database.  My script basically follows ta number of steps. I delete the entire existing table including all extra columns and replace it by loading the new file and rebuilt all the new columns. If you have an idea for a different / better approach please let me know. 
eggem01 (rep: 2) Jun 12, '20 at 4:38 am
I didn't mean to criticize your system, nor do I believe that I have the means to judge its efficacy. However, the purpose of a formula is to be able to extract changing data from a fixed location or a location that changes with changing parameters in the formula. If there isn't a good reason for wanting a formula in your output you should consider using VBA to extract the data. In its simplest execution you would just use the same worksheet function in VBA and enter the data in CF2 instead of the formula.
Even if that should make perfect sense you may well decide not to implement the idea right now because your system is working and you want to spend your time on other things. It isn't all black and white. But it's always good to know an alternative and to know the pros and cons of both what you could have and what you do have. I meant to suggest the alternative. You judge the pros and cons.
Variatus (rep: 4889) Jun 12, '20 at 5:55 am
Hello Variatus
No worries. If I would ask someone for help I am not going to criticize his answer. On the contrary, your answer gave me stuff to think about. I think I understand the point you make, and it certainly is valid for a number of columns. I will use VBA to get the data instead of entering a formule that will get the data.
However columns with a vlookup are a different story. They make sure that the incoming data is connected to the latest other tables (f.e. employee data). Using a formula makes sure that changed employee data is available by just refreshing the pivot tables. I believe this is what you mean in your sentence starting with " However".
As a non-professional developer I am always looking for ways to improve the code, or to make it look more professional. Your answer is definitely helpful in that way. The biggest problem I always face is that when I have a problem and find a solutions myself or somewhere at the net that works, it isn't always a solution a pro would use. My VBA knowledge is growing but is not always good enough to see a better and obvious solution.
eggem01 (rep: 2) Jun 13, '20 at 7:53 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login