INDEX+MATCH ArrayFormula Return 1004

1
votes

I've been using StackOverflow for months and it has answered all of my questions, expect for the issue I'm currently having. I can't seem to fix the issue, no matter the options posted on this site. Maybe you can see the issue, that I'm not seeing at the moment.

I have one FormulaArray in VBA that takes the index of an external file, matches one value (Q2) with a value in column B of the external file. Then it matches a second value (P2) with a value in column D of the external file. If there's a match on both, it'll return a value that is in column C of the external file.

Works great in Excel, but the moment I create a Selection.FormulaArray = this formula, I get a 1004 in return. The line itself is shorter than 255 characters and even breaking up the INDEX and MATCH formulas into two parts (by Dim formulapart1 and formulapart2 as string) it doesn't work.

=INDEX([ISOCODE.xlsx]ISOCODE!$A:$D;MATCH(1;([ISOCODE.xlsx]ISOCODE!$B:$B=Q2)*([ISOCODE.xlsx]ISOCODE!$D:$D=P2);0);3)

I tried the following solution that was posted on this website that worked for others, but it's not working for me, no matter what I do:

Dim theFormulaPart1 As String
Dim theFormulaPart2 As String

theFormulaPart1 = "=INDEX([ISOCODE.xlsx]ISOCODE!$A:$D;XX"
theFormulaPart2 = "MATCH(1;([ISOCODE.xlsx]ISOCODE!$B:$B=Q2)*([ISOCODE.xlsx]ISOCODE!$D:$D=P2);0);3)"

With ActiveSheet.Range("DS2")
    .FormulaArray = theFormulaPart1
    .Replace "XX", theFormulaPart2
End With
2
vbaexcelexcel-formula
1. The formula has to be viable so instead of XX use 999. 2. when using vba to insert a formula, one needs to use en-us format. replace the ; with , - Scott Craner
Thanks a lot @ScottCraner! I was about to flip the table in the office after spending two hours on this formula, coming to find out this simple solution. Thanks a lot for waking me up, mate! - Yassin Kulk
@ScottCraner Why does the formula have to be split in two, is it too long for VBA to insert? - dwirony
As @ScottCraner said, theFormulaPart1 needs to be viable as a stand-alone formula - currently, you're not closing the brackets! Try theFormulaPart1="=INDEX([ISOCODE.xlsx]ISOCODE!$A:$D;999;3)", and remove ;3)` from the end of theFormulaPart2 - Chronocidal
Thanks a lot @Chronocidal. I got it all working. This thing made me nuts. Dwirony is right by the way, it's all working at the moment. We're working in a French environment (although I'm dutch and working with an en-US environemnt). I have this continuous issue with the semicolon and comma, not knowing that the comma was necessary in the VBA script. Thanks a lot all, all sorted! - Yassin Kulk

2 Answers

1
votes

There are issues with how you have split the code (your theFormulaPart1 is not a valid formula on its own!) but that's not the real issue here - that's your "XY Problem".

The real issue is that when putting a Formula in via VBA, unless you are using .FormulaLocal, you must use the English localisations - this means using , intead of ; to separate the arguments. And, there is no .FormulaArrayLocal, so for an Array formula you always use the English localisation:

ActiveSheet.Range("DS2").FormulaArray = "=INDEX([ISOCODE.xlsx]ISOCODE!$A:$D,MATCH(1,([ISOCODE.xlsx]ISOCODE!$B:$B=Q2)*([ISOCODE.xlsx]ISOCODE!$D:$D=P2),0),3)"
1
votes

Whenever working with excel-VBA (or with anything in general), try always to start from something smaller, that works. In your case, remove the opend worksheet and try to make it work with the one you have. Thus, here is something that works:

Sub TestMe()

    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String

    theFormulaPart1 = "=INDEX(A:D,XX"
    theFormulaPart2 = "MATCH(1,(B:B=Q2)*(D:D=P2),0),3)"

    With ActiveSheet.Range("E1")
        theFormulaPart1 = Replace(theFormulaPart1, "XX", theFormulaPart2)
        .FormulaArray = theFormulaPart1
    End With

End Sub

Once you fix it, go further and include [ISOCODE!.xlsx]