R1C1 to A1 inaccurate formula conversion
Double up your double quotes in your code.
Rng.FormulaR1C1 = "=IF(RC[-1]="","",COUNTIF(R2C14:R5000C14,RC[-1]))"
becomes
Rng.FormulaR1C1 = "=IF(RC[-1]="""","""",COUNTIF(R2C14:R5000C14,RC[-1]))"
markx wrote:
Hello once again,
I use the following macro (suggested - in its original version - by Tom
Hutchins, thanks!):
Sub Test2()
Dim x As Long, Rng As Range
x& = Range("L" & Rows.Count).End(xlUp).Row
Set Rng = Range("O2:O" & x&)
Rng.FormulaR1C1 = "=IF(RC[-1]="","",COUNTIF(R2C14:R5000C14,RC[-1]))"
Set Rng = Nothing
End Sub
I have a problem converting the formula from:
=IF(RC[-1]="","",COUNTIF(R2C14:R5000C14,RC[-1]))
To
=IF(N2="";"";COUNTIF($N$2:$N$5000;N2))
In fact, it converts to:
=IF(N2=",";COUNTIF($N$2:$N$5000;N2))
Any idea what am I doing wrong?
Thanks,
Mark
--
Dave Peterson
|