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

 

[tiredofit]

I see where you're coming from with your 1bn figure now

Thanks for your reply.

Looking at the data I have, it seems (because I don't have live data) the answer to your 12 criteria is yes, which makes the problem more complicated.

As I said, I've already written the code using loops and it works but is slow.

Using vlookups and index / match is faster but owing to the 255 character limit:

http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

I have to split things up.

 

[jasonb75]

If all 12 are potentially set then it will most likely be slow however you do it.

If the data is sorted on those columns in both sheets, then you might be able to speed things up a bit.

If you post your current loop code then it would give us an idea of what you have tried rather than starting from scratch and coming up with the same thing.