Replace array formula with VBA

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
I was under the impression that using index / match or vlookup is quicker than looping but I could be wrong :)
 
Upvote 0
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.
 
Upvote 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.

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]
 
Upvote 0
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


Book1
EFG
3123ABCD123ABCD
4123ABCD123ABCD
Sales


ie exactly the same thing.

How many rows of data do you have?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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