Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum inaccurate | Excel Worksheet Functions | |||
Question on this conversion code switching between r1c1 to A1 format tia sal2 | Excel Worksheet Functions | |||
Question on this conversion code switching between r1c1 to A1 format tia sal2 | Excel Discussion (Misc queries) | |||
Question on this conversion code switching between r1c1 to A1 format tia sal2 | Excel Worksheet Functions | |||
R1C1 Formula in Excel | Excel Worksheet Functions |