Replace array formula with VBA

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
I am trying to replace the evaluate method with "only VBA".

This first piece of code is a "normal" index match, in that there is only a single criteria and so is entered simply by pressing the Enter key.

a and b return the same value, as expected.

Rich (BB code):
    Dim IndexRng As Range
    Set IndexRng = Range("A1:A2")
    
    Dim rng As Range
    Set rng = Range("B1:B2")
    
    Dim rng1 As Range
    Set rng1 = Range("E3")
    
    Dim a, b
        
    a = Evaluate("=INDEX(" & IndexRng.Address & ",Match(" & rng1.Address & "," & rng.Address & ", 0), 1)")

    b = Application.Index(IndexRng, Application.Match(rng1.Value, rng, 0), 1)





This second piece of code is an array formula because there are multiple criteria and is entered by pressing Ctrl+Shift+Enter.

c returns the correct value but d errors.


Rich (BB code):
    Dim IndexRng As Range
    Set IndexRng = Range("A1:A2")
    
    Dim rng As Range
    Set rng = Range("B1:B2")
    
    Dim rng2 As Range
    Set rng2 = Range("C1:C2")
    
    Dim rng3 As Range
    Set rng3 = Range("E3")
    
    Dim rng4 As Range
    Set rng4 = Range("F3")
    
    Dim c, d
    
    c = Evaluate("=INDEX(" & IndexRng.Address & ",Match(1, (" & rng3.Address & " = " & rng.Address & ") * (" & rng4.Address & "=" & rng2.Address & "),0))")
    
    d = Application.Index(IndexRng, Application.Match(1, (rng3 = rng) * (rng4 = rng2), 0, 1))



The error message is:

Rich (BB code):
Run-time error '13':

Type mismatch
How can I change d to make it work?

Thanks
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,720
Office Version
365
Platform
Windows
You can change the formula to a non CSE formula like
=INDEX(A1:A2,MATCH(E3&"|"&F3,INDEX(B1:B2&"|"&C1:C2,0),0))

BUT you said you wanted "only VBA", neither version is "only VBA" as you are using worksheet Functions.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,385
Office Version
2019
Platform
Windows
To omit the evaluation you would need to loop through the rows checking each one individually until a match is found.
Code:
    Dim IndexRng As Range, crit1 As Range, crit2 As Range, rw As Range, d As Variant
    
    Set IndexRng = Range("A1:C3")
    Set crit1 = Range("E3")
    Set crit2 = Range("F3")
    
    For Each rw In IndexRng.Rows
        If rw.Cells(1, 2).Value = crit1.Value And rw.Cells(1, 3).Value = crit2.Value Then
            d = rw.Cells(1, 1).Value
            Exit For
        End If
    Next
Debug.Print d
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
You can change the formula to a non CSE formula like
=INDEX(A1:A2,MATCH(E3&"|"&F3,INDEX(B1:B2&"|"&C1:C2,0),0))

BUT you said you wanted "only VBA", neither version is "only VBA" as you are using worksheet Functions.

Thanks.

Are you saying to write this:

Rich (BB code):
d =INDEX(A1:A2,MATCH(E3&"|"&F3,INDEX(B1:B2&"|"&C1:C2,0),0))


I did that and got a compile error.

To omit the evaluation you would need to loop through the rows checking each one individually until a match is found.
Code:
    Dim IndexRng As Range, crit1 As Range, crit2 As Range, rw As Range, d As Variant[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Set IndexRng = Range("A1:C3")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Set crit1 = Range("E3")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Set crit2 = Range("F3")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    For Each rw In IndexRng.Rows[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        If rw.Cells(1, 2).Value = crit1.Value And rw.Cells(1, 3).Value = crit2.Value Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            d = rw.Cells(1, 1).Value[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            Exit For[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Next[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Debug.Print d
Thanks but I've got too much data, so didn't want to loop.


<strike></strike>
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,385
Office Version
2019
Platform
Windows
A loop might be faster than an array.

An array has to process every row of data in the range. The loop stops when it finds a match so it might only need to process a small percentage of the data.

Another option would be to apply filters by vba based on the criteria in E3 and F3, then grab the value of the first visible row.
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
I was under the impression that using index / match or vlookup is quicker than looping but I could be wrong :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,720
Office Version
365
Platform
Windows
Are you saying to write this:

Rich (BB code):
d =INDEX(A1:A2,MATCH(E3&"|"&F3,INDEX(B1:B2&"|"&C1:C2,0),0))
Nope, that is the worksheet formula, so you would need to convert it to VBA in the same way you have with the two formulae you showed.
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
Nope, that is the worksheet formula, so you would need to convert it to VBA in the same way you have with the two formulae you showed.
OK, so I did this:

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    d = Application.Index(IndexRng, Application.Match(rng3 & "|" & rng4, Application.Index(rng & "|" & rng2, 0), 0))
replacing the E3 with rng3, F3 with rng4, etc.

but I still got a Type mismatch error.

BTW, what's the significance of the |?


[/FONT]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,720
Office Version
365
Platform
Windows
It looks like VBA doesn't like handling array formulae in that manner. I suspect you will either need to use Evaluate, or ditch the functions & use pure VBA as suggested by Jason.
The "|" is just a delimiter to prevent false positives, for instance if you had these values in E & F col G shows what you would get without a delimiter

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">123</td><td style=";">ABCD</td><td style=";">123ABCD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">123A</td><td style=";">BCD</td><td style=";">123ABCD</td></tr></tbody></table><p style="width:4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sales</p><br /><br />

ie exactly the same thing.

How many rows of data do you have?
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
It looks like VBA doesn't like handling array formulae in that manner. I suspect you will either need to use Evaluate, or ditch the functions & use pure VBA as suggested by Jason.
The "|" is just a delimiter to prevent false positives, for instance if you had these values in E & F col G shows what you would get without a delimiter

EFG
3123ABCD123ABCD
4123ABCD123ABCD

<tbody>
</tbody>
Sales



ie exactly the same thing.

How many rows of data do you have?

Thanks for the explanation.

Prob a few hundred thousand rows of data!

Using pure VBA took ages, so I resorted to array formulae and index match / vlookups instead of looping, which vastly improved the running time.

My latest attempt is to let Access do the reconciling. It's super fast but the problem is having to import the data from Excel into the Access tables, which takes more time than the rec itself.

Another obstacle is the 2GB limit in Access.

So perhaps I'll have to ask for SQL Server.



 
Last edited:

Forum statistics

Threads
1,085,463
Messages
5,383,840
Members
401,856
Latest member
trikiash

Some videos you may like

This Week's Hot Topics

Top