# 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:

=verweis(A3;$Zutaten.$A$3:$Zutaten.$A$100;$Zutaten.$E$3:$Zutaten.$E$100)*B3/1000


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

But the cell which contains the formula just shows

#NAME?


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

edit retag close merge delete

Sort by » oldest newest most voted

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

more

awsome, thank you! propably never would have guessed this

( 2019-11-09 19:11:58 +0100 )edit

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

more