R1C1 to A1 inaccurate formula conversion
 

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

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

R1C1 to A1 inaccurate formula conversion
 

Thanks, it works great!
(should have found it by myself :-((

"Dave Peterson" wrote in message
...
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