Converting sheet formular which incudes operators into VBA .formula

[Upex]

Hi All,

For the life of me I can't get this working, despite throwing quotes all over it.

The sheet has a formula in it at present:

=IF(COUNTIF($H$5:$H$50000,"<"&">"&"")=0,"",COUNTIF($H$5:$H$50000,"<"&">"&""))

Which simply counts the nonblank cells (likely not the best way but hey, it works) with the range of column H.

I want to delete the formula and put the formula into a code module, so I can have it add the formula in, calculate, then copy the result as the cell value (i.e. so the formula is not visible on the sheet, just the result.

I'll not post all the code, as everything is working (except for the insertion of the operators) and its large, but I've tried entering the above sheet formula directly, i.e.

.Range("i3").Formula = "=IF(COUNTIF($H$5:$H$50000,"<"&">"&"")=0,"",COUNTIF($H$5:$H$50000,"<"&">"&""))"

Had to double up the "" 's, to:

.Range("i3").Formula = "=IF(COUNTIF($H$5:$H$50000,"<"&">" &"""")=0,"""",COUNTIF($H$5:$H$50000,"<"&">" &""""))"

Tried quoting the operators as well;

.Range("i3").Formula = "=IF(COUNTIF($H$5:$H$50000,"""<"""&""">""" &"""")=0,"""",COUNTIF($H$5:$H$50000,"""<"""&""">""" &""""))"

Then tried 'unquoting' the operators:

.Range("i3").Formula = "=IF(COUNTIF($H$5:$H$50000,<> &"""")=0,"""",COUNTIF($H$5:$H$50000,<> &""""))"

Can't get it to work. Any ideas on how operators need to be handled when being used within .formula ?

Thanks all, ​Upex

 

[Upex]

Sussed it with alittle help from the macro recorder:

Range("i3").Formula = "=IF(COUNTIF($H$5:$H$50000,""<""&"">""&"""")=0,"""",COUNTIF($H$5:$H$50000,""<""&"">""&""""))"

Far too many """'s