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
 
Sort by date Sort by votes
Fluff said:
There is nothing in the code you posted that assigns a value to Lastrow, so is it a global variable?
Click to expand...
I'm not sure I fully understand what you mean. The range "F" is set in the start of the code.
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I am talking about you variable called Lastrow, that is used in the formula. Where is it getting it's value from?
 
Upvote 0
It's taking from the L column. Try looking in the ordinary formulas, which have to be Copied down, and then they will produce a list of range in the L Column, where the Index formula, look at..
 
Upvote 0
Can you please answer my question?
 
Upvote 0
Fluff said:
Can you please answer my question?
Click to expand...
I'll try my best. But still, I'm not sure I understand you.
I can put in, how it looks, when range L is copied down in the example, and that's where the formula look in F to match the L value.

test-index.xlsm
ABCDEFGHIJKLMNOPQ
1
2
321-07-2021301370314VM00027374,29007825602114VM0002737FALSK -0,05763514,6806388
421-07-2021301370414VM0002737-0,057634882114VM0003031-5,625878    
522-07-2021301370514VM00027374,680638751014VM0002735
622-07-2021301370614VM0003031-5,625878245014VM0002495
722-07-2021301370714VM00027356,333088848014VM0002623
822-07-2021301370814VM00024954,866441490014VM0002329
922-07-2021301370914VM0002623-0,241104024114VM0002718
1022-07-2021301371014VM00023297,084553141014VM0003278
1122-07-2021301371114VM00027182,275177043114VM0002702
1222-07-2021301371214VM00032785,563642157014VM0002494
1322-07-2021301371314VM00027025,587300185014VM0003025
1422-07-2021301371414VM00024941,963967872114VM0002625
1522-07-2021301371514VM00030251,067954037114VM0002414
1622-07-2021301371614VM0002625-3,641699777014VM0002449
1722-07-2021301371714VM00024141,381285221114VM0002446
1822-07-2021301371814VM00024492,240493636114VM0002630
1922-07-2021301371914VM00024462,386489200114VM0002076
2022-07-2021301372014VM00026305,212646248014VM0002018
2122-07-2021301372114VM00020761,739811762114VM0002198
2222-07-2021301372214VM00020182,183079615114VM0003210
2322-07-2021301372314VM0002737-0,254155390114VM0003064
2422-07-2021301372514VM0002735-0,28881595010
sep21
Cell Formulas
RangeFormula
N3:N4,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))),""))
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:D24D3=WEEKNUM(C3,2)
H3:H24H3=IF(G3="","",IF(AND(G3>-3.5,G3<3.5),"1","0"))+0
L3:L24L3=IFERROR(INDEX($F$3:$F$1000,MATCH(0,COUNTIF($L$2:L2,$F$3:$F$1000),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
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
 
Upvote 0
In you code you have
Rich (BB code): 
Sheets("sep21").Range("L3") = FormulaArray = "=IFERROR(INDEX($F$3:$F$" & Lastrow & ",MATCH(0,COUNTIF($L$2:L2,$F$3:$F$" & Lastrow & "),0)),"")"
where are you giving the variable in red a value?
 
Upvote 0
Fluff said:
In you code you have
Rich (BB code): 
Sheets("sep21").Range("L3") = FormulaArray = "=IFERROR(INDEX($F$3:$F$" & Lastrow & ",MATCH(0,COUNTIF($L$2:L2,$F$3:$F$" & Lastrow & "),0)),"")"
where are you giving the variable in red a value?
Click to expand...
I see now, what you mean :oops: I was missing the declarations. Can I go with .used range, or do I have to declare a specific range ?

Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
 
Upvote 0
Fluff said:
That should work. :)
Click to expand...
I got it to work. I had a lot of other codes in my Head, at the moment, so I was a bit confused. But, I can see, that it will not work proberly, in that way of doing it. It takes extremely long time for the code to run, to take out the data from the list, and place them in the correct columns.
So i have to find another way around, to do it.
 
Upvote 0
Glad you got it sorted to some extent & thanks for the feedback.
 
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
424
Holley
H
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
315
Skyybot
Skyybot
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
199
Tara_A_Data
T
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
209
StephenCrump
S
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
124
flashgordie
F

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

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