Macro for entering Vlookup formula



I have seen a video from this site to enter formula with macro;

Sub Enter Formulas ()

Range("D4:D7").FORMULA = "=AVARAGE (B4:C4)"

End Sub

I need to do same with below formula

Sub Enter_Formulas()

Range("J7:J10").Formula = "=IF(ISBLANK(B7);"";(VLOOKUP(B7;NTNTOPS;9;FALSE)))"

End Sub

NTOPS is sheetname

application  or objecst define error is ocuring. What is wrong?

Thank you?



Selected Answer

Before asking VBA to write many formulas for you it's a good idea to test one of them. This is your formula. Try it.


In plain language, your formula instructs to look for the value in B7 in the first column  of Sheet NTNTOPS and return the value found in the 9th column of that sheet from the row where a match was found. Excel can't do that. Amend your instruction as follows.

"Look for the value in B7 in the first column of the specified range and return the value found in the 9th column of that specified range from the row where a match was found.".

As you see, VLOOKUP requires you to specify a range, not a sheet, and that range must have at least 9 columns. This is what your specification might look like.


If you build that into the formula to test it might work. However, you should also look at relative and absolute referencing. As you copy your formula to J7:J10 the reference to B7 will change in each row to B8, B9 and B10. This is probably your intention and therefore good.

The referenced range will change similarly, from C2:M32 to C3:M33, C4:M34 and C5:M35 and that is unlikely to be part of your design. Therefore the reference style should be changed to absolute, like this.


Observe that I omitted the $ sign for the column references. If you don't intend to copy your formulas left and right, only up and down, that $ sign would be superfluous. Personally, I don't like such lose ends. Therefore I would make all column references absolute in this formula, even the one for $B7.

But, while on the subject of personal preference, I urge you to consider why you should use VBA to write a formula. VBA is perfectly capable of writing the result. VBA allows you much more flexibility in specifying every part of the formula in much plainer syntax. Using a worksheet function you are hemmed in by very strict rules from which you aren't permitted to deviate by one iota. Using VBA instead you may find the resulting code not as "simple" but finding errors and making amendments is easier, not in the least because you have only once possible source of error instead of two.



Thank you for your reply.
as you are right VLOOKUP needs range. 
Sheet  name is NT that VLOOKUP find the data and   range is B1:K100 formula is;
=VLOOKUP(B7;NT!B1:K100;10;FALSE ) perfectly running on J7 cell.

Than I put this on the macro;
Sub NTLoader() Sheets("OPTIONS").Select Range("J7").Formula = "=VLOOKUP(B7;NT!B1:K92;9;FALSE)" End Sub But I have still same problem;"application  or objecst define error" is ocuring.

Quequeg (rep: 4) Mar 13, '18 at 4:10 pm
Your code runs fine on my PC. However, I get the same error if I don't replace the semi-colons in your code with commas. Please try that. The semi-colon is used as separator instead of commas in MS Office installations for some European languages.
Also note that you don't need to select the sheet you wish to write to. Here is the simplified code with commas: Sheets("OPTIONS").Range("J7").Formula = "=VLOOKUP(B7,NT!B1:K92,9,FALSE)"
Variatus (rep: 2205) Mar 13, '18 at 8:58 pm
Yes, it is working now. Thank you very much.
Macro is running with commas and it is creating real formula on the cell with semi colon and it is running in the cell also ..
Quequeg (rep: 4) Mar 14, '18 at 3:55 pm
I'm glad we got it to work. Please remember to mark the answer as "Selected". Thank you.
Variatus (rep: 2205) Mar 14, '18 at 8:28 pm
Add to Discussion

Answer the Question

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