Ask Your Question
0

Formulas inserted by macro not evaluated

asked 2019-11-08 17:37:30 +0100

mantolion gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2019-11-08 17:39:10 +0100

updated 2019-11-08 17:39:40 +0100

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

edit flag offensive delete link more

Comments

awsome, thank you! propably never would have guessed this

mantolion gravatar imagemantolion ( 2019-11-09 19:11:58 +0100 )edit
0

answered 2019-11-08 18:14:40 +0100

mauricio gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-08 17:37:30 +0100

Seen: 14 times

Last updated: Nov 08