runtime error 1004 when setting a certain formula from vba 1

[mberni]

Hi!
I'd like to fill cells with a certain formula from a vba-makro.

I have tried the following :

dim li_zeile as integer
dim li_ld as integer
dim lm_lipr as double
dim lm_rwp as double
dim lm_rab as double
li_zeile=1
li_ld=36
lm_lipr=50000
lm_rab=10
lm_rwp=40

With Workbooks(aktuell).Sheets(aktsheet).Cells(li_zeile, 14)
.Value = "=RMZ(" + Format(Round(5.35 / 12 / 100, 5), "0.00000") + ";" + Str(li_ld) + ";" + Format(Round(-(lm_lipr) * (100 - lm_rab) / 100, 2), "###0.00") + ";" + Format(Round(lm_lipr * lm_rwp / 100, 2), "###0.00") + ")"
End With

the resulting formula will look like this (checked this with debugger):

=RMZ(0,00446; 36;-45000,00;20000,00)

when i copy and paste this formula into some excel-cell i get the correct result:
842,43 €

But my code always raises runtime-error 1004.

when i simply set the formula "=sum("a1:b1") for a try, everything is fine.

what is going wrong with the "rmz"-formula here?

Many thanks.

 

[Zathras]

Instead of .Value = ....
try .Formula = ....

 

[PHV]

I think you should use the .FormulaLocal property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mberni]

Hello!

Thanks for your quick response.

.formula didn't work, i already tried that. Thanks for the post anyways.

.formulalocal did the job. Many thanks.

It seems the problem here is the localization of the formula-language.