Evaluating filter function formula.

[Herakles]

I am trying to use the EVALUATE function to populate an array with the results of a filter but get a mismatch error.

What am I dong wrong?

This is not a real project, I'm just practising.

Thanks

Private Sub testIt()
Dim arr() As Variant
    
    arr = Evaluate("IFERROR(INDEX(FILTER(tblNames,tblNames[Group]<>""""), SEQUENCE(3,1,3,1), SEQUENCE(1, COLUMNS(tblNames))), """")")

End Sub

Multi-level Bill Of Materials explosion.xlsm
	A	B	C	D	E	F	G	H
1	Name	Group	Wins		Group	B
2	Item 1	C	1		Rows	2
3	Item 2	A	6		Start	6
4	Item 3	C	4
5	Item 4	C	5		Item 1	C	1
6	Item 5	A	7		Item 1	C	1
7	Item 6	A	9
8	Item 7	C	3
9	Item 8	A	7
10	Item 9	B	4
11	Item 10	C	6
12	Item 11	B	3
13	Item 12	B	5
14	Item 13	B	7
Names

Cell Formulas
Range	Formula
F1	F1	=CHOOSE(RANDBETWEEN(1,3),"A","B","C")
F2	F2	=RANDBETWEEN(1,4)
F3	F3	=RANDBETWEEN(1,7)
E5:G6	E5	=IFERROR(INDEX(FILTER(tblNames,tblNames[Group]<>""), SEQUENCE($F$2,1,$G$2,1), SEQUENCE(1, COLUMNS(tblNames))), "")
Dynamic array formulas.

 

[Fluff]

Try it like

Private Sub testIt()
Dim arr As Variant
    
    arr = Evaluate("IFERROR(chooserows(FILTER(tblNames,tblNames[Group]<>""""), SEQUENCE(3,1,3,1)), """")")

End Sub

 

[Herakles]

Try it like

Private Sub testIt()
Dim arr As Variant
   
    arr = Evaluate("IFERROR(chooserows(FILTER(tblNames,tblNames[Group]<>""""), SEQUENCE(3,1,3,1)), """")")

End Sub

That makes much more sense.

Thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Domenic]

For what it's worth, the reason why you're getting a mismatch error is that when the INDEX portion of the formula is evaluated it actually returns a string, not an array as one would expect...

    Dim arr As Variant
    
    arr = Evaluate("INDEX(FILTER(tblNames,tblNames[Group]<>""""),SEQUENCE(3,1,3,1),SEQUENCE(1,COLUMNS(tblNames)))")
    
    Debug.Print TypeName(arr)

However, we can return an array of values from INDEX like this...

    Dim arr As Variant
    
    arr = Evaluate("INDEX(FILTER(tblNames,tblNames[Group]<>""""),N(IF(1,SEQUENCE(3,1,3,1))),N(IF(1,SEQUENCE(1,COLUMNS(tblNames)))))")
    
    Debug.Print TypeName(arr)

In any case, @Fluff has provided you with a much more efficient alternative.

Hope this helps!