Vba formula entry problem

[VHS01]

=COUNTIF(C9:C39,"<.43") entered into a cell works fine
I need to have code enter the formula

Trying to add it programically like this:
Range("C40").Formula = "=COUNTIF(C9:C39,"<.43")"
Gives the following red colored entry error
Compile error
Expected: end of statement

Changing to this:
Range("C40").Formula = "=COUNTIF(C9:C39,<.43)"
Gets rid of the entry error, but gives the following error when it runs
Run-time error ‘1004’:
Application-defined or object-defined error

Please help!

 

[Andrew Poulsom]

The best way to find out the syntax is to record a macro while doing it manually. This is what I got:

ActiveCell.FormulaR1C1 = "=COUNTIF(R[-31]C:R[-1]C,""<.43"")"

The recorder uses the FormulaR1C1 property, but the important thing to note is the doubling up of the quotes around the criteria. So using the Formula property it would be:

Range("C40").Formula = "=COUNTIF(C9:C39,""<.43"")"

 

[VHS01]

Thank you for the speedy reply