Using VBA Array Formula without R1C1 notation?

[hankistan]

Hi Everyone.

I am a novice and this is my first post, but this site has solved many issue for me in the past. Anyway, onto my issue.

I have a data file with 3 sections, and I'm doing calculations on the middle section. For the data that I want counted, I've been using code like this:

With Range("S170").End(xlDown).Offset(3, 0)
.Value = Application.WorksheetFunction.CountIf(Range("S170", Range("S170").End(xlDown)), "Y")
End With

because the number of rows in the middle section is variable. That works great, but now I have a more complicated calculation; namely, an AVERAGEIF array formula. If I record a macro of the formula it of course uses the R1C1 notation.

    Selection.FormulaArray = _
        "=AVERAGE(IF((R[4]C[-4]:R[19]C[-4]>0),R[4]C[-4]:R[19]C[-4]))"

I assume that there is a way to use the Ranges as I have in the first example, but I'm getting lost in the syntax. Any assistance would be greatly appreciated.

Thanks,
Hank


P.S. I'm running Excel 2010 and Windows 7

 

[Andrew Poulsom]

What's wrong with R1C1 notation? It's much more flexible than A1 notation in VBA. Here's an explanation of it:

About cell and range references - Excel - Office.com

 

[JackDanIce]

This works for me, you may need to adjust the ranges etc:

i = Range("S" & Rows.Count).End(xlUp).row
Range("S" & i + 3).FormulaArray = "=AVERAGE(IF((O4:O" & i & " >0),O4:O" & i & "))"

 

[hankistan]

Thanks Andrew and Jack for your quick replies!

Andrew- the problem with R1C1 as I understand it for my data is that since I have a section of a file that varies in length across days, r1c1 isn't flexible enough for what I"m doing. Of course it could be my novice level that limiting it's use.

Jack - thanks, this looks pretty good. I have 2 issues with it though:
1. The condition in that IF statement that I need to meet is if (O4:O" & i & " ="Y") and that "Y" is not working in the code (I get a compile error expected: end of statement)
2. Because this is that middle section of the data file using xlUp is also problematic; is there a way to re-write this with xlDown?

Thanks again!
Hank

 

[JackDanIce]

Try:

i = Range("S170").End(xlDown).row
Range("S" & i + 3).FormulaArray = "=AVERAGE(IF((O4:O" & i & " =""Y""),O4:O" & i & "))"

 

[hankistan]

Jack,

you=lifesaver

Thanks, Happy Holidays!

-Hank

 

[JackDanIce]

You're welcome, thanks for the feedback