Index array formulas problems in VBA, giving FALSE error

ebea

ebea

Active Member
Joined
Jul 12, 2008
Messages
302
Office Version
  1. 2021
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
321-07-2021301370314VM00027374,290078256021FALSKFALSK 010101010100
421-07-2021301370414VM0002737-0,057634882114VM00027374,2900783 -0,05763514,6806388 -0,25415514,42868   53
522-07-2021301370514VM00027374,6806387510
622-07-2021301370614VM0003031-5,6258782450
722-07-2021301370714VM00027356,3330888480
822-07-2021301370814VM00024954,8664414900
922-07-2021301370914VM0002623-0,2411040241
1022-07-2021301371014VM00023297,0845531410
1122-07-2021301371114VM00027182,2751770431
1222-07-2021301371214VM00032785,5636421570
1322-07-2021301371314VM00027025,5873001850
1422-07-2021301371414VM00024941,9639678721
1522-07-2021301371514VM00030251,0679540371
1622-07-2021301371614VM0002625-3,6416997770
1722-07-2021301371714VM00024141,3812852211
1822-07-2021301371814VM00024492,2404936361
1922-07-2021301371914VM00024462,3864892001
2022-07-2021301372014VM00026305,2126462480
2122-07-2021301372114VM00020761,7398117621
2222-07-2021301372214VM00020182,1830796151
2322-07-2021301372314VM0002737-0,2541553901
2422-07-2021301372514VM0002735-0,2888159501
2522-07-2021301372614VM00024950,2115647761
2622-07-2021301372714VM0002623-0,7973959241
2722-07-2021301372914VM00027180,2386747211
2822-07-2021301373014VM00032780,1833377191
2922-07-2021301373114VM0002702-0,0319706251
3022-07-2021301373214VM0002494-0,1023513781
3122-07-2021301373314VM00030250,2372470211
3222-07-2021301373414VM00026250,1472767971
3322-07-2021301373514VM00024140,1885001771
3422-07-2021301373614VM00024490,2253080981
3522-07-2021301373714VM0002446-0,4244404541
3622-07-2021301373814VM00026300,2168644871
3722-07-2021301373914VM00020760,3145244641
3822-07-2021301374014VM00020180,2347803941
3922-07-2021301374114VM00027374,4286800120
4022-07-2021301374214VM00021985,7016345920
4122-07-2021301374314VM00027356,0514882930
4222-07-2021301374414VM00024955,2522212620
4322-07-2021301374514VM00032105,7614066580
4422-07-2021301374614VM0003064-1,8153527061
4522-07-2021301374714VM00027182,4639970731
4622-07-2021301374814VM00032786,2733713140
4722-07-2021301374914VM00027025,6951859590
4822-07-2021301375014VM00024943,0990406031
sep21
Cell Formulas
RangeFormula
N3:N4,X3:X4,V3:V4,T3:T4,R3:R4,P3:P4N3=IFERROR(IF(M3="","",IF(AND(M3>-3.5,M3<3.5),"1",""))+0,"")
O3:O4O3=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:Q4Q3=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:S4S3=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:U4U3=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:W4W3=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:Y4Y3=IF(L3<="","",COUNTIF($F$3:$F$7000,L3))
Z3:Z4Z3=IF(L3>"",COUNTIFS($F$3:$F$7000,L3,$H$3:$H$7000,"=0"),"")
L4L4=IFERROR(INDEX($F$3:$F$1000,MATCH(0,COUNTIF($L$2:L3,$F$3:$F$1000),0)),"")
M4M4=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:D48D3=WEEKNUM(C3,2)
H3:H48H3=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
CellConditionCell FormatStop If True
Z3:Z4Cell Value>0textNO
Z4Cell Value=0textNO
Z3Cell Value=0textNO
M3:X4Cell Value<-3,5textNO
M3:X4Cell Value>3,5textNO
I3:I4Cell Value=TEGN(252)textNO
I3:I4Cell Value>TEGN(251)textNO
J4Cell Value=0textNO


VBA kode, below here.

VBA Code: 
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
It should be
VBA Code: 
Sheets("sep21").Range("L3").FormulaArray
otherwise you are doing a logical comparison which is why you get False.
 
Upvote 0
Fluff said:
It should be
VBA Code: 
Sheets("sep21").Range("L3").FormulaArray
otherwise you are doing a logical comparison which is why you get False.
Click to expand...
Unfortunately, it gives me an error 1004. (Application-defined, or definition error)
 
Upvote 0
You also need to double up on the quotes in the formula like
VBA Code: 
"=IFERROR(INDEX($F$3:$F$" & Lastrow & ",MATCH(0,COUNTIF($L$2:L2,$F$3:$F$" & Lastrow & "),0)),"""")"
 
Upvote 0
Fluff said:
You also need to double up on the quotes in the formula like
VBA Code: 
"=IFERROR(INDEX($F$3:$F$" & Lastrow & ",MATCH(0,COUNTIF($L$2:L2,$F$3:$F$" & Lastrow & "),0)),"""")"
Click to expand...
It still give the same error, Fluff
 
Upvote 0
Fluff said:
What is Lastrow?
Click to expand...
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
 
Upvote 0
Where is lastrow getting it's value from, there is nothing for it in the code you posted?
 
Upvote 0
Fluff said:
Where is lastrow getting it's value from, there is nothing for it in the code you posted?
Click to expand...
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.
 
Upvote 0
There is nothing in the code you posted that assigns a value to Lastrow, so is it a global variable?
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Query not pulling right info when running in VBA
2
Replies
13
Views
393
Holley
H
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
294
Skyybot
Skyybot
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
Tara_A_Data
T
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
204
StephenCrump
S
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
197
sanrama
S

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top