EXCEL 2010: Why does macro record different to what I type?

[JayCR]

Hi Guys,

Hope someone can help.

Basically I am recording / writing a macro to allow me to count all the rows before and after filter.

so I start the Macro recording and type into cell A6

=COUNTIF((G2:G999999),"Void")

The Macro then records:

ActiveCell.FormulaR1C1 = "=COUNTIF((R[-4]C[6]:R[999993]C[6]),""Void"")"

Then I type into Cell A9

=COUNTIF((G2:G99999),"Purc")

The Macro then records:

ActiveCell.FormulaR1C1 = "=COUNTIF((R[-7]C[6]:R[99990]C[6]),""Purc"")"

So why is it that I choose to type in the above and its changed what I typed? why has it put the additional information in against what I typed?

Can anyone shed any light on this? and does this mean in fact it is not go as far counting down to cell "999999"?

[seenu_1st]

Jay, there r 2 reference methods
1. Absolute method = $A$1
2. Relative method, it's from activecell, say for example if Activecell = B2 then reffer to A1 like this
R[-1]C[-1]
so the macro records using relative method, hope u got it.

[kaliman79912]

What seenu_1st said. Since you are positioned on cell A6 (row 6, column 1) and the reference in your formula is G2 (row 2, column 7), excel macro recorder figures you want to reference 4 rows above and 6 columns to the right R[-4]C[6], leaving the reference relative. So if you apply it to another cell it will do exactly that, relative reference.

A couple of things: It is in fact counting down to cell "G999999".
If you have a fixed range type it as $G$2:$G$999999.
Or you can go in and edit the code the macro wrote.

[JayCR]

Thanks Guys,

So basically I can replace the relative method like for like what I have put in the cell?

so replace

ActiveCell.FormulaR1C1 = "=COUNTIF((R[-4]C[6]:R[999993]C[6]),""Void"")"
with
=COUNTIF((G2:G999999),"Void")

I have realised I can get rid of the G999999 just by replacing it with G:G to highlight the entire column...

[kaliman79912]

Yes, you could. It is not necesary though as it is exactly the same, exept if you are posting the same formula in many different cells.