use of VBA for AVERAGEIF array formula

[sts8500man]

I have a set of non-adjacent cells whose numerical contents I need to average. The cells are all in row 10 of my “table” and are each 4 columns apart starting at cell J10 and ending at cell CX10. Using cell CY10 of the “table” I unput the array formula:
=AVERAGE(IF(MOD(COLUMN(J10:CX10),4)=2,IF(ISNUMBER(J10:CX10),J10:CX10)))

It worked just fine to provide proper results while skipping cells consisting of text or zero values.

However, I need that formula to work from a VBA and there it does not seem to work. I would like to know why. The macro I wrote is:

Sub Calc_CY10()

'

' Calc_CY10 Macro
Range("CY10").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(IF(MOD(COLUMN(J10:CX10),4)=2,IF(ISNUMBER(J10:CX10),J10:CX10)))"
ActiveWorkbook.Save
End Sub

The error message I get is:
#NAME?

And the formula displayed in the formula bar is different from the formula written into the macro. It is:
=AVERAGE(IF(MOD(@COLUMN('J10':'CX10'),4)=2,IF(ISNUMBER(@'J10':'CX10'),'J10':'CX10'))).

The fact that this works in cell CY10 is acceptable, but I prefer it to work from a VBA application “run”. I don’t know why it won’t work from the macro when it works fine in the cell, nor do I know how to correct the macro so that it will work from VBA.

Would appreciate some help with this.

 

[Fluff]

Use Formula2 instead of FormulaR1C1

 

[sts8500man]

Thank you so much for your response to my need. It works like a charm. I can't say that I understand why "formula2" works and "formulaR1C1" does not work. I appreciate your help a whole bunch.

STS8500MAN

 

[Fluff]

You are using A1 notation in the formula rather than R1C1 notation which was part of the problem, but the @ signs where inserted because the formula could spill, so to deal with that you need to use Formula2 rather than Formula

 

[sts8500man]

Oh yeah - understand. I should have recognized that! Again, thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback.