Replace array formula with VBA

[tiredofit]

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.

    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.

    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:

Run-time error '13':

Type mismatch

How can I change d to make it work?

Thanks

 

[Fluff]

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]

To omit the evaluation you would need to loop through the rows checking each one individually until a match is found.

    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]

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:

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.

    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>
​

 

[jasonb75]

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]

I was under the impression that using index / match or vlookup is quicker than looping but I could be wrong

 

[Fluff]

Are you saying to write this:

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]

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:

[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]

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
	E	F	G
3	123	ABCD	123ABCD
4	123A	BCD	123ABCD
Sales

ie exactly the same thing.

How many rows of data do you have?

 

[tiredofit]

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

	E	F	G
ABCD	123ABCD
123A	BCD	123ABCD

<tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]

[TD="align: center"]4[/TD]

</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.



​