Recurrence frequency report

[montecarlo2012]

Hello everyone.
I am trying to generate a recurrence frequency report and this is what I have been doing manually, (to long by hand)
=FREQUENCY(B1:G36,I1:I53)
=FREQUENCY(B2:G37,I1:I53)
=FREQUENCY(B3:G38,I1:I53) and so on until I reach =FREQUENCY(B18:G53,I1:I53)
I want the out put in J:AA
my data is B1:G53
any vba code for this please.

 

[shg]

In J1:J53,

{=FREQUENCY(INDEX($B:$B, COLUMNS($J1:J1)):INDEX($G:$G, COLUMNS($J1:J1) + 35), $I$1:$I$53)}

Copy right.

 

[montecarlo2012]

I really appreciate your time for reading my post, also I really copy carefully, but the results are iqual 0.
and because I have to reapet this many time, thats why my TAG IS VBA CODE.
THANK YOU shg.

 

[shg]

Code to do the same thing:

    With Range("J1:J53")
        .FormulaArray = "=FREQUENCY(INDEX($B:$B, COLUMNS($J1:J1)):INDEX($G:$G, COLUMNS($J1:J1) + 35), $I$1:$I$53)"
        .AutoFill .Resize(, 18)
    End With

 

[montecarlo2012]

HIGHLITE
COPY
DEVELOPER
VISUAL BASIC
INSERT
MODULE
PASTE
RUN

no results.

 

[shg]

Dunno, works fine for me.

What happens -- nothing, or an error?

Sheet protected? Macros enabled? All cells either locked or unlocked, not some of each?

 

[montecarlo2012]

Dunno, works fine for me.

What happens -- nothing, or an error?

Sheet protected? Macros enabled? All cells either locked or unlocked, not some of each?

sheet portected? ....no
macros enabled?.....no
error?....................no
just not results at all.

 

[shg]

Did you add Sub and End Sub? Post your code.

 

[montecarlo2012]

yes, sub range()
the code here

end sub

 

[shg]

I have no suggestion. If you want to upload it to box.net and post a link, I'll look at it.