Formulas inserted by macro not evaluated

Hi there!

I am trying to write a macro that will copy some strings and values from one table to another and add formulas that work with said strings and values.

The copying works fine, but when i insert the formulas they do not evaluate.
This is the macro code in question:

cocktailauswahl.getCellByPosition(2, linesCopied).Formula = "=VERWEIS(A" & (linesCopied+1) & ";$Zutaten.$A$3:$Zutaten.$A$100;$Zutaten.$E$3:$Zutaten.$E$100)*B" & (linesCopied+1) & "/1000"

And it works to the point that the cells contain this formula:


(the number 3 is correctly calculated by the macro, no problems there)

But the cell which contains the formula just shows


A workaround i found is to replace every ‘=’ in the document with ‘=’, which triggers a (re-)evaluation of the formulas. Additionaly, after the replacing, the function name is written in all caps and i am suspecting the problem having to do something with this.

So my question is whether i do something wrong or how i can resolve this.

Thanks in advance, i will provide mor information if needed.

btw VERWEIS is the german equivalent of LOOKUP

In macros, you need to use English function names, not localized ones.

awsome, thank you!
propably never would have guessed this

You can used FormulaLocal, but, is better used it Formula and function name in English