Not able to enter a formula in a cell

[chamster]

The code itself is not finished yet. I'm posting it for the examples sake only. Please note - the error is NOT with incompleteness of the formula, even if it might seems so.

I'm trying the following code
.Cells(i, dSpan + 2).Formula = "=SUM.IF(" & "" & Chr(34) & ">0" & Chr(34) & ")"
and it executes fine (as in: i can compile and put that stuff in the cell). The fact that it gives error as the value matters not.

Then i change that to
.Cells(i, dSpan + 2).Formula = "=SUM.IF(" & "A" & Chr(34) & ">0" & Chr(34) & ")"
i.e. put in a capital A and guess what - the compiler doesn't like it anymore... What's up with that?!

[Aussiebear]

How does the SUM.If bit work?

[chamster]

According to what i used before and what i've read in the help files it is as follows.

SUM.IF(range;condition)

and it sums only those elements that fulfill the condition.

Now, perhaps i should ask the question from a different angle.

1. How can i enter a dynamically updating formula for computing mean of elements in a row-range but only those that are larger than 5?

2. How can i enter a dynamically updating formula that computes the number of elements in a row-range larger than 5?

[johnske]

avoid the Chr(34), read post #7 by XLD here (http://www.vbaexpress.com/forum/showthread.php?t=15352&referrerid=391)