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.

`=VLOOKUP(B7;NTNTOPS;9;FALSE)`

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.

`NTNTOPS!C2:M32`

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.

`=IF(ISBLANK(B7);"";(VLOOKUP(B7;NTNTOPS!C$2:M$32;9;FALSE)))"`

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.