Error with formula syntax

HI, i wirte for an important problem that maybe has got a silly solution, but anyway i can't understand it.

I'm working with easy formula syntax like:

Worksheets(3).Cells(23, 7).formula = "=sum('Distribution and Chart'!G7:G22)"

Well, i've putted a control button on my main worksheet (1) and i insert a vb code in it.
This code is very simple... it's just a succesion of formula syntaxs made to insert some formulas in some cells in other worksheets.

Now arrive my problem, for easy formula like sum i can normaly write a syntax like the one up,
but when i wanna insert in a cell a formula more complicated like:

Worksheets(2).Cells(r, c).formula = "=sumif('Popolazione & Campioni'!$C$8:$C$107;INT(100*rand())+1;'Popolazione & Campioni'!$B$8:$B$107)"

dosen't work anything...
when i run my macro clicking on the button an error message goes out:

RunTime Error '1004'
Application-defined or object-defined error


I've tried everything.... changing the criterion cells range... changing name of the worksheets, erasing the $ in the formula, inserting some value taking place of the variable r & c, but nothing... it sends me the same error if i just put "=sumif()"
I've seen that if i put " =sumif()" with an initial space it works perfectly...

This error dosen't come with other formulas like sum, averange... but it comes with Frequency, min, max, sumif, normdist... so... with this kind of formula....

I really don't understand what could it be.. cause i've recorded my macro... i've saved it, my button works in the right way and my syntax too... but maybe there is something that i don't know about this kind of formula... i really wanna know how can i put those in a cell... maybe there is another syntax or another trick.

Thank you very much.

VBA works with American English and this goes all the way to the separators in the functions, so although you use ; as a separator you must use , instead when setting the Formula property.

Please Login or Register  to view this content.

HTH

TJ

Are you sure you have got the right fucntion there, as NORMSDIST only takes one parameter.

From the help file:

Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas.

Syntax

NORMSDIST(z)

Z is the value for which you want the distribution.

TJ