Array formula in VBA sheet

[alanhather]

Hi all,

I'm trying to input this array formula in VBAand its filling in the cell C180. Any ideas?

The sheet5 is called admissons

Sub Array_Formula()


Range("C180").Select
Selection.FormulaArray = "=SUM(--(FREQUENCY(IF('Raw Data Prev Year'!$B:$B<>"",IF('Raw Data Prev Year'!$D:$D=A180,IF('Raw Data Prev Year'!S:S>=$B$167,IF('Raw Data Prev Year'!$S:$S<=$C$167,MATCH('Raw Data Prev Year'!$B:$B,'Raw Data Prev Year'!$B:$B,0))))),ROW('Raw Data Prev Year'!$B:$B)-ROW('Raw Data Prev Year'!$B$1)+1)>0))"


End Sub

Thanks

 

[steve the fish]

What is that formula meant to do? It just looks like its can only produce a 1 or a 0 based on whether the conditions exist or not. For that a countif may be better. Anyway it wont work because its too long as an array formula for VBA to handle. Its possible to get around that but first say what its meant to be doing.

 

[alanhather]

Hi - this is counting a certain name within a date range between certain dates but counting a unique ID once if duplicated. It works but thought it would be better through VBA.

 

[steve the fish]

Yeah so its checking if all the conditions are met and if so produces a 1 and if not a 0? See if this produces the same results. It doesnt need array entry:

=0+(COUNTIFS('Raw Data Prev Year'!$D:$D,A180,'Raw Data Prev Year'!$B:$B,"<>",'Raw Data Prev Year'!S:S,">="&$B$167,'Raw Data Prev Year'!S:S,"<="&$C$167)>0)

 

[alanhather]

Hi as you said this is producing 1 - it should produce 16 as i have 17 records but 1 of them is a duplicate so the array formula counted it as 1 and returned 16.

 

[alanhather]

Hi did you have any suggestion for this?

thanks