• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

R1C1 Quick Conversion Tool

I

indi visual

Member
Normally, because I have no idea what I'm doing (lol), I record a macro and type in the formula. Once I view what the macro recorded I get the R1C1 version.


I need the R1C1 version of the following formulas:

[pre]<br />
=COUNTIF(Z1,"0")<br />
=COUNTIF(Z1,"1")<br />
=COUNTIF(Z1,"2")<br />
=COUNTIF(Z1,"3")<br />
=COUNTIF(Z1,"4")<br />
=COUNTIF(Z1,"5")<br />
=COUNTIF(Z1,"6")<br />
=COUNTIF(Z1,"7")<br />
=COUNTIF(Z1,"8")<br />
=COUNTIF(Z1,"9")<br />
=COUNTIF(Z1,"10")<br />
=COUNTIF(Z1,"11")<br />
=COUNTIF(Z1,"12")<br />
=COUNTIF(Z1,"13")<br />
=COUNTIF(Z1,"14")<br />
=COUNTIF(Z1,"15")<br />
=COUNTIF(Z1,"16")<br />
=COUNTIF(Z1,"17")<br />
=COUNTIF(Z1,"18")<br />
=COUNTIF(Z1,"19")<br />
=COUNTIF(Z1,"20")<br />
=COUNTIF(Z1,"21")<br />
=COUNTIF(Z1,"22")<br />
=COUNTIF(Z1,"23")<br />
=COUNTIF(Z1,"24")<br />
=COUNTIF(Z1,"25")<br />
[/pre]


I tried what I normally do as mentioned above, but for one reason or another when I test the version the macro recorded, it doesn't translate quite right.


Any ideas on how to get this translated to R1C1?


Side note: Any translation help with the formulas above is appreciated, but I cannot have them condensed, they actually all have to remain separate for what I'm doing.
 
Hi,


The formula is not clear, are you Counting the items in one cell Z1 only, or are you trying to Count for the column.


To see what the R1C1 formula would be you can set Excel to R1C1 style in the Excel options for formulas. But be aware of the that R1C1 is usually a relative reference from the cell you working unless the reference in the formula is an absolute reference.


Let me know what you are trying to achieve with the formuls.


cheers


kanti
 
I think I figured it out...

[pre]<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-37]C, ""0"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-38]C, ""1"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-39]C, ""2"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-40]C, ""3"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-41]C, ""4"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-42]C, ""5"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-43]C, ""6"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-44]C, ""7"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-45]C, ""8"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-46]C, ""9"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-47]C, ""10"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-48]C, ""11"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-49]C, ""12"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-50]C, ""13"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-51]C, ""14"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-52]C, ""15"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-53]C, ""16"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-54]C, ""17"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-55]C, ""18"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-56]C, ""19"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-57]C, ""20"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-58]C, ""21"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-59]C, ""22"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-60]C, ""23"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-61]C, ""24"")"<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-62]C, ""25"")"<br />
[/pre]


I need to clear the contents of a cell, and then immediately pop it right back in. In order to do that, I needed the R1c1 formula for excel to place the formula back into the cell correctly.


I'm doing all of this for an audio alarm. The 0 through 25 you see are the different wave files it will play. However, I have to immediately pop the formula in and out or it won't shut up (it'll keep repeating forever or crash <or both>).


Now, my page calculates automatically, and the alarm function only goes off once when the specified cell changes. It was a lot of work (and a lot of code for just an alarm function), and I have to wait 4 seconds each time the alarm goes off... but the pay off and professional feel was well worth it.


As for my original posted question.. my question was not clear (that and I didn't exactly know what I was asking for), so that wasn't your fault it was mine, and I definitely appreciate the help.
 
You must log in or register to reply here.
Back
Top