Formula Array (CSE) in VBA

[Roderick_E]

Hi there all. I know how to do a sheet1.cells(x,y).formularray = "=formula" but I'm trying to accomplish something a bit different.

Using INDEX MATCH within VBA I can return a simple single column match, such as:

sheet1.cells(x,y) = application.index(sheet2.range("A:A"),application.match(string,sheet2.range("B:B"),0),1)

However, what if I wanted do match multiple criteria via VBA? In a formula you would do something like the following but requires control+shift+enter {...}:

=index(sheet2.range("A:A"),match(string1&string2,sheet2.range("B:B")&sheet2.range("C:C"),0 ),1)

This would return a string from column A if string1&string2 matching together in columns B and C.

QUESTION: How do I make this work in VBA?
Excel 2003-2010 in use
Thanks

 

[mhillmann]

Couldn't you just use an if then statement in VBA?

Something like...

If Range("B1") = string1 And Range("C1") = string2 then
    ' return A
end if

I am not sure of the exact implementation but I hope that helps

 

[Roderick_E]

Well, the exact row in the range is unknown. I could use a loop For X statement but it is extremely slow processing

 

[mhillmann]

This might be more helpful

If Sheet1.Cells(intRow, "B") = string1 And Sheet1.Cells(intRow, "C") Then
    Sheet1.Cells(intRow, intCol) = Cells(intRow, "A")
End If

 

[mhillmann]

Well, the exact row in the range is unknown. I could use a loop For X statement but it is extremely slow processing

Oh I see what you mean.

 

[mhillmann]

Maybe this is what you are looking for?

https://msdn.microsoft.com/en-us/library/office/ff837104.aspx

 

[Roderick_E]

yeah, the .formularray solution inserts a formula into the cell, not really what I wanted to do. I could probably do something like that and then turn it into a string but kind of clunky. Thanks for trying to help. I'll keep looking and post here if I find a solution.

 

[Roderick_E]

*sigh* can't find a solution

 

[Roderick_E]

Ok, I'm getting closer using EVALUATE
My actual code snippet:

'match defaults
On Error Resume Next
evalstr = "INDEX(" & "'" & Sheet2.Name & "'!E:E,MATCH(""" & vorgcitystr & """ & """ & vdstcitystr & """ & """ & vorgportstr & """ & """ & vdstportstr & """, '" & Sheet2.Name & "'!A:A" & "&" & "'" & Sheet2.Name & "'!B:B" & "&" & "'" & Sheet2.Name & "'!C:C" & "&" & "'" & Sheet2.Name & "'!D:D,0),1)"
If IsError(Evaluate(evalstr)) = False Then
Sheet3.Cells(i, "g") = Evaluate(evalstr)
End If
Resume Next

Seems like evaluate can't handle too many processes. Anyone have specs?