Index array formulas problems in VBA, giving FALSE error

[ebea]

Hi! I have a very large dataset (+100000 rows), where I should take out data, based on dates, and out of these, again list some of these data, in Columns.
this works fine, by using Array index formulas. But to try to speed up the proces, I set these same formulas, into a VBA solution.
But when I run this VBA, the results end up in a FALSE return (L3 and M3), but if you look at L3 and M4 the "normally" array formulas work.

So any of you in here, which have an idea on how to solve this, or maybe another idea, to speed up the process, list these Columns using VBA.

test-index.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3			21-07-2021	30	13703	14VM0002737	4,290078256	0			21	FALSK	FALSK		0	1	0	1	0	1	0	1	0	1	0	0
4			21-07-2021	30	13704	14VM0002737	-0,057634882	1				14VM0002737	4,2900783		-0,057635	1	4,6806388		-0,254155	1	4,42868				5	3
5			22-07-2021	30	13705	14VM0002737	4,680638751	0
6			22-07-2021	30	13706	14VM0003031	-5,625878245	0
7			22-07-2021	30	13707	14VM0002735	6,333088848	0
8			22-07-2021	30	13708	14VM0002495	4,866441490	0
9			22-07-2021	30	13709	14VM0002623	-0,241104024	1
10			22-07-2021	30	13710	14VM0002329	7,084553141	0
11			22-07-2021	30	13711	14VM0002718	2,275177043	1
12			22-07-2021	30	13712	14VM0003278	5,563642157	0
13			22-07-2021	30	13713	14VM0002702	5,587300185	0
14			22-07-2021	30	13714	14VM0002494	1,963967872	1
15			22-07-2021	30	13715	14VM0003025	1,067954037	1
16			22-07-2021	30	13716	14VM0002625	-3,641699777	0
17			22-07-2021	30	13717	14VM0002414	1,381285221	1
18			22-07-2021	30	13718	14VM0002449	2,240493636	1
19			22-07-2021	30	13719	14VM0002446	2,386489200	1
20			22-07-2021	30	13720	14VM0002630	5,212646248	0
21			22-07-2021	30	13721	14VM0002076	1,739811762	1
22			22-07-2021	30	13722	14VM0002018	2,183079615	1
23			22-07-2021	30	13723	14VM0002737	-0,254155390	1
24			22-07-2021	30	13725	14VM0002735	-0,288815950	1
25			22-07-2021	30	13726	14VM0002495	0,211564776	1
26			22-07-2021	30	13727	14VM0002623	-0,797395924	1
27			22-07-2021	30	13729	14VM0002718	0,238674721	1
28			22-07-2021	30	13730	14VM0003278	0,183337719	1
29			22-07-2021	30	13731	14VM0002702	-0,031970625	1
30			22-07-2021	30	13732	14VM0002494	-0,102351378	1
31			22-07-2021	30	13733	14VM0003025	0,237247021	1
32			22-07-2021	30	13734	14VM0002625	0,147276797	1
33			22-07-2021	30	13735	14VM0002414	0,188500177	1
34			22-07-2021	30	13736	14VM0002449	0,225308098	1
35			22-07-2021	30	13737	14VM0002446	-0,424440454	1
36			22-07-2021	30	13738	14VM0002630	0,216864487	1
37			22-07-2021	30	13739	14VM0002076	0,314524464	1
38			22-07-2021	30	13740	14VM0002018	0,234780394	1
39			22-07-2021	30	13741	14VM0002737	4,428680012	0
40			22-07-2021	30	13742	14VM0002198	5,701634592	0
41			22-07-2021	30	13743	14VM0002735	6,051488293	0
42			22-07-2021	30	13744	14VM0002495	5,252221262	0
43			22-07-2021	30	13745	14VM0003210	5,761406658	0
44			22-07-2021	30	13746	14VM0003064	-1,815352706	1
45			22-07-2021	30	13747	14VM0002718	2,463997073	1
46			22-07-2021	30	13748	14VM0003278	6,273371314	0
47			22-07-2021	30	13749	14VM0002702	5,695185959	0
48			22-07-2021	30	13750	14VM0002494	3,099040603	1
sep21

Cell Formulas
Range	Formula
N3:N4,X3:X4,V3:V4,T3:T4,R3:R4,P3:P4	N3	=IFERROR(IF(M3="","",IF(AND(M3>-3.5,M3<3.5),"1",""))+0,"")
O3:O4	O3	=IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:N3))),""))
Q3:Q4	Q3	=IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:O3))),""))
S3:S4	S3	=IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:P3))),""))
U3:U4	U3	=IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:Q3))),""))
W3:W4	W3	=IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:R3))),""))
Y3:Y4	Y3	=IF(L3<="","",COUNTIF($F$3:$F$7000,L3))
Z3:Z4	Z3	=IF(L3>"",COUNTIFS($F$3:$F$7000,L3,$H$3:$H$7000,"=0"),"")
L4	L4	=IFERROR(INDEX($F$3:$F$1000,MATCH(0,COUNTIF($L$2:L3,$F$3:$F$1000),0)),"")
M4	M4	=IF(L4="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L4,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:M4))),""))
D3:D48	D3	=WEEKNUM(C3,2)
H3:H48	H3	=IF(G3="","",IF(AND(G3>-3.5,G3<3.5),"1","0"))+0
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z3:Z4	Cell Value	>0	text	NO
Z4	Cell Value	=0	text	NO
Z3	Cell Value	=0	text	NO
M3:X4	Cell Value	<-3,5	text	NO
M3:X4	Cell Value	>3,5	text	NO
I3:I4	Cell Value	=TEGN(252)	text	NO
I3:I4	Cell Value	>TEGN(251)	text	NO
J4	Cell Value	=0	text	NO

VBA kode, below here.

Sub Unique()
     Dim rData As Range
     
     With Sheets("sep21")
        Set rData = .Range("F3", .Range("F" & Rows.Count).End(xlUp))
     End With
                
    Sheets("sep21").Range("K3").Value = _
         Evaluate(Replace("=SUM(IF(FREQUENCY(IF(sep21!@<>"""",MATCH(sep21!@,sep21!@,0))," _
          & "ROW(sep21!@)-ROW(sep21!F3)+1),1))", "@", rData.Address))
          
        Sheets("sep21").Range("L3") = FormulaArray = "=IFERROR(INDEX($F$3:$F$" & Lastrow & ",MATCH(0,COUNTIF($L$2:L2,$F$3:$F$" & Lastrow & "),0)),"")"
       
       Sheets("sep21").Range("M3") = FormulaArray = ("=IFERROR(INDEX($G$3:$G$" & Lastrow & ",SMALL(IF($F$3:$F$" & Lastrow & "=$L3,ROW($F$3:$F$" & Lastrow & ")-ROW($F$3)+1),COLUMNS($M$3:M3)),""))")
              
End Sub

 

[Fluff]

It should be

Sheets("sep21").Range("L3").FormulaArray

otherwise you are doing a logical comparison which is why you get False.

 

[ebea]

It should be

Sheets("sep21").Range("L3").FormulaArray

otherwise you are doing a logical comparison which is why you get False.

Unfortunately, it gives me an error 1004. (Application-defined, or definition error)

 

[Fluff]

You also need to double up on the quotes in the formula like

"=IFERROR(INDEX($F$3:$F$" & Lastrow & ",MATCH(0,COUNTIF($L$2:L2,$F$3:$F$" & Lastrow & "),0)),"""")"

 

[ebea]

You also need to double up on the quotes in the formula like

"=IFERROR(INDEX($F$3:$F$" & Lastrow & ",MATCH(0,COUNTIF($L$2:L2,$F$3:$F$" & Lastrow & "),0)),"""")"

It still give the same error, Fluff

 

[Fluff]

What is Lastrow?

 

[ebea]

What is Lastrow?

it's 48 (only part of large set, in this example). I also, tried define the row itself, in the formula, instead of LastRow, But same result, in these two VBA formulas

 

[Fluff]

Where is lastrow getting it's value from, there is nothing for it in the code you posted?

 

[ebea]

Where is lastrow getting it's value from, there is nothing for it in the code you posted?

From the index in Column F. It's as the formula in either L4 or M4.
The VBA example of the Index formulas, do work as ordinary Array formulas, but as you see, not as VBA solution.

 

[Fluff]

There is nothing in the code you posted that assigns a value to Lastrow, so is it a global variable?