VB Help

[alanhather]

Hi -

Im trying to enter an array formula into VB if possible - i know the formula is too big but struggling to get it to work.

the formula im using is entered in Cell C180 and looks like this.

=SUM(--(FREQUENCY(IF('Raw Data Prev Year'!$B:$B<>"",IF('Raw Data Prev Year'!$D:$D=A171,IF('Raw Data Prev Year'!S:S>=$B$168,IF('Raw Data Prev Year'!$S:$S<=$C$168,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))

it is used to count an unique Alphanumeric ID within a specific date range and name and it only counts the alphanumeric ID once if it is a duplicate. As im doing a few calculations it can take a while to calculate but i do get the result.

Is this possible to run in VB or is there a way i can use as a countif?

thanks in advance

 

[steve the fish]

This is ugly and cumbersome but its the only way to get a long array formula into a cell via VBA i know of:

aaa = "=SUM(--(FREQUENCY(IF('Raw Data Prev Year'!$B:$B<>"""",IF(bbb,IF(ccc,IF(ddd,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))"
bbb = "'Raw Data Prev Year'!$D:$D=A171"
ccc = "'Raw Data Prev Year'!S:S>=$B$168"
ddd = "'Raw Data Prev Year'!$S:$S<=$C$168"

With Range("C180")
    .FormulaArray = aaa
    .Replace "bbb", bbb
    .Replace "ccc", ccc
    .Replace "ddd", ddd
End With

 

[alanhather]

Thanks for the help i'll give it a try now. - Do you think this could be done as a Count if function at all or is it too complex?

 

[alanhather]

Sorry do i need to use this as a sub? something like Sub ArrayFormula then put End sub at the end?

Thanks

 

[steve the fish]

Well yes its code. You asked for VBA. You also need to be in the correct sheet when you run it, ie the sheet where you want it placed.

 

[alanhather]

Thanks worked really well this did. Is there a way though to use dates from another sheet? I have created a control sheet with cells with the dates on it so the year changes each time automatically.

The array formula doesnt seem to like using this and only likes a cell with the date typed in.

Looks like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]01/01[/TD]
[TD]=today() 'this is for the year[/TD]
[TD]=YEAR(D3) 'for the year[/TD]
[TD]=A1&"/"&$A$3[/TD]
[/TR]
[TR]
[TD]31/01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is so i don't have to change the dates each time and pre set up.

thanks

 

[steve the fish]

Heres how to build a date that changes when the year changes. I think thats what you mean.

=DATE(YEAR(TODAY()),1,1)

That will produce 1st Jan of current year.