VBA Match+Index formulas call for multiple criteria

[MarioMacias]

Hi! I've using cycle macros for searching in each column certain value and then comparing values in adjacent columns in order to get values to show or similar matters
At this moment I found out that there is a way to call formulas from vba code via Application.WorksheetFormulas
and at the same time I found the Match+Index formulas for multiple criteria by arrange {}

Is there a way to use the ArrayFormulas from VBA??

My idea is something like the one as follow...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Or Target.Address = "$D$2" Then
If ActiveSheet.Range("D1") = "" Or ActiveSheet.Range("D2") = "" Then
MsgBox = ("Complete data before start")
Else
'criteria to search
Val1 = ActiveSheet.Range("D1").Value
Val2 = ActiveSheet.Range("D2").Value
'columns to search each value
RangeVal1 = ActiveSheet.Range("A:A")
RangeVal2 = ActiveSheet.Range("B:B")
Matrix = ActiveSheet.Range("A:C")
'My problem is at the formula assigned to Result that only works when it is in an array "{=index...}"
Result = Application.WorksheetFunction.Index(Matrix, Application.WorksheetFunction.Match(Val1 & Val2, RangeVal1 & RangeVal2, 0), 3)
ActiveSheet.Range("D3").Value = Result
End If
End If
End Sub


Any ideas will be welcome
Best regards!

 

[bsquad]

you need it in an array because you are matching two different values to two different ranges.

you would just need to use .FormulaArray = "=yourformula"
one thing to keep in mind, inserting arrays cap off your formula length by 255 characters, so if your formula is longer than that (which array formulas usually are), you will also have to use it in conjunction with .Replace
-The replace values have to also have an underscore in it (theres also another character I think that will work, a semicolon maybe, I don't remember for sure)

I'll give you an example -

Dim Area_11, Area_12, Area_13, Area_14, Area_15 As String
    
Application.DisplayAlerts = False:Application.StatusBar = X17
        Area_11 = "=IFERROR(INDEX($A$16:$A$5000,MATCH(LARGE(IF(COUNTIF($L$15:L15,X_2)=0,SUMIF(X_2,X_2,X_3)/SUMIF(X_2,X_2,X_4),""""),1),SUMIF(X_2,X_2,X_3)/SUMIF(X_2,X_2,X_4)*(COUNTIF($L$15:L15,X_2)=0),0)),"""")"
        Area_12 = "INDIRECT(""$A$16:A$""&MATCH(0,$D$16:$D$5000,-1)+14)"
        Area_13 = "INDIRECT(""$C$16:C$""&MATCH(0,$D$16:$D$5000,-1)+14)"
        Area_14 = "INDIRECT(""$D$16:D$""&MATCH(0,$D$16:$D$5000,-1)+14)"
        Area_15 = "=INDEX($A$16:$A$3500,MATCH(LARGE($D$16:$D$3500-ROW($D$16:$D$3500)/COUNT($D$16:$D$3500),ROW(L45)-ROW(L$45)+1),$D$16:$D$3500-ROW($D$16:$D$3500)/COUNT($D$16:$D$3500),0))"
            With Sheet210.Range("L16")
                    .FormulaArray = Area_11
                    .Replace "X_2", Area_12
                    .Replace "X_3", Area_13
                    .Replace "X_4", Area_14
            End With

 

[MarioMacias]

But in this case you are applying it to set the formula on a range or cell, right??
I was thinking about this to avoid using cycles like the following

sub example ()

 

[MarioMacias]

sub example ()
do while not isempty(activecell.value)
if activecell.value="x" and activecell.offset(0,1).value="y" then
var=activecell.offset(0,2).value
end if
activecell.offset(1,0).activate
loop

and that variable is something I commonly use to make strings as ticket names or similar stuff.
thanks for your help!

 

[bsquad]

yes, but you can also can convert it to a value if you're worried about its volatility. Otherwise try evaluate if you're looking for it to be 'live'