Newbie Needs some help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Folks

Have been trying to create my first attempt at a macro, and need some basic help regarding 2 items

Ques
I want to create a results based on a range of data. ie If countif a>1 / count (G1:G5), where the results displayed would be say 2 / 5. I've managed to code it but don't understand how to convert the RC values to A1:B1 type values. Here is code which work versus my guess.

ActiveCell.FormulaR1C1 = "=CONCATENATE(COUNTIF(Sheet1!RC[5]:R[7]C[5],""<1""),"" /"",COUNT(Sheet1!RC[5]:R[7]C[5]))

ActiveCell.FormulaR1C1 = "=CONCATENATE(COUNTIF(range(Sheet1!G2:G6)),""<1""),"" /"",COUNT(range(Sheet1!G2:G6)))

Can someone explain how I can convert the RC to A:B type.

Ques 2
I want to do a similar thing but have a criteria which is countif A between 1 and 1.3 (>1<1.3) / count(G2:G5)

thanks in advance....
 
The "equivalent" to your first line of code in A1 format is:

ActiveCell.Formula =
"=CONCATENATE(COUNTIF(Sheet1!F1:F8,""<1""),""/"",COUNT(Sheet1!F1:F8))"

IF the active cell is A1. Be sure that you understand the difference
between using the R1C1 and A1 reference styles though. There is no true
equivalent of the R1C1 macro in A1 format. The range referenced with the
R1C1 macro will always be dependent on what is the active cell when the
macro is run (the referenced range will start 5 columns to the right of it).
The A1 macro (above) will always reference the range F1:F8 regardless of the
active cell.
 
Thanks, that worked a treat... I understand the RC1 problem, thats why I preferred to be able to write in A1 notation...
 
Back
Top