Evaluating A Filter Formula.

[HighAndWilder]

I'd like to be able to use the formula in cell L3 in VBA.

I've tried this but I get unexpected results.

Any insights appreciated.

Thanks

Sub TestIt()
Dim lngRow

  lngRow = Evaluate("IFERROR(TAKE(FILTER(ROW($A$2:$A$13),($C$2:$C$13=5)*($G$2:$G$13=10)),1),0)")

  ' This produces an Error 13 Type Mismatch.
  MsgBox lngRow

  ' BUT this assigns the correct value, 4, to cell M3.
  Range("M3").Value = lngRow

End Sub

Sort array of files in folder - by Date Modified.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	C 1	C 2	C 3	C 4	C 5	C 6	C 7	C 8	C 9
2	8	10	4	6	3	5	9	3	7
3	6	6	8	6	4	5	7	4	2		Row	4
4	5	5	5	3	7	2	10	4	3
5	2	4	4	4	7	1	8	9	10
6	4	10	1	4	5	3	2	3	2
7	2	10	7	8	3	6	7	8	4
8	8	10	4	6	3	5	9	3	7
9	6	6	8	6	4	5	7	4	2
10	8	5	10	6	7	9	4	1	3
11	5	10	3	8	3	3	5	1	2
12	6	9	9	6	1	4	6	3	9
13	2	5	5	1	4	8	3	5	9
14
Sheet2

Cell Formulas
Range	Formula
L3	L3	=IFERROR(TAKE(FILTER(ROW($A$2:$A$13),($C$2:$C$13=5)*($G$2:$G$13=10)),1),0)

 

[Fluff]

lngRow is an array so you would need to use

  MsgBox lngRow(1)

 

[HighAndWilder]

lngRow is an array so you would need to use

  MsgBox lngRow(1)

Of course.

Thanks Fluff.

 

[Fluff]

You're welcome.