R1C1 to A1 inaccurate formula conversion

  • Thread starter Thread starter markx
  • Start date Start date
M

markx

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
 
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]))"


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
Click to expand...
 
Thanks, it works great!
(should have found it by myself :-((

Dave Peterson said:
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]))"


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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 1
R1C1 Formula 2
Unique values from list of many values (with duplicates) 5
COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A" 1
Inaccurate count with code 6
My attempt to do a Replace 9
If cell value = x or y then perform formula set else skip 3
Formula returning an error 2

Back
Top