AutoFill

[Derek McGill]

Hi all.
When I try to AutoFill

Range("P2").Formula = "=SUMPRODUCT((O1:O111 <> "")/COUNTIF(O1:O111,O1:O111 & ""))"
It removes 2 of the "'s Ie:-
=SUMPRODUCT((O1:O111 <> ")/COUNTIF(O1:O111,O1:O111 & "))

Any help on how to fix this ?

 

[SirJB7]

Hi, Derek McGill!

Try this:
Range("P2").Formula = "=SUMPRODUCT((O1:O111 <> """")/COUNTIF(O1:O111,O1:O111 & """"))"

Since you're assigning a string (that contains a formula, but a string at last), you have to embed it in quotes. And if the inner string (aka the formula) uses quotes, they should be doubled so as to be considered as a single character in the assignment.

Regards!

 

[Derek McGill]

Thank you for a good explanation.

 

[SirJB7]

Hi, Derek McGill!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!