Excel VBA Array Formula using Index Match giving an error Type mismatch

Question

I'm trying to add an Array formula into a Table column on a worksheet. I'm using Index Match and splitting the formula because it gets quite big with all the file names. I use it in other parts of the code by splitting it and it works (but using different references, etc).

I keep getting an error "Type mismatch" when I try and replace/insert the Match formula into the Index formula. When I do it manually (by copying the variable string values) it works...

ATPFile = Dir(DataPath & "\*ATP*COMBINED.xls*")
ATPFileF = DataPath & "\" & ATPFile

Set wb = Workbooks.Open(ATPFileF)

'Add ATP data from ATP raw data file
sheetname = Worksheets("ATP").Name

'Get last row with data in ATP file
Dim rowCountSource As Integer
Dim ATPRowRange As Range
Dim sFomrula2 As String

With wb.Worksheets("ATP")
    rowCountSource = .Cells(.Rows.Count, 2).End(xlUp).Row
End With

ref = DataPath & "\[" & ATPFile & "]" & sheetname

sFormula = "=INDEX('" & ref & "'!$P$1:$P$" & rowCountSource & ",""X_X_X"",1)"
sFormula2 = "MATCH([@Article]&[@Site],('" & ref & "'!$B$1:$B$" & rowCountSource & ")&('" & ref & "'!$D$1:$D$" & rowCountSource & "),0)"

'Add ATP info
With ws.ListObjects("Table_DCvenSDCdata")
    With .ListColumns("ATP").DataBodyRange
        .NumberFormat = "General"
        With .Cells(1, 1)
            .FormulaArray = sFormula
            .Replace """X_X_X""", sFormula2
        End With
        .FillDown
        .Value = .Value
    End With
End With

The error pops up at .Replace """X_X_X""", sFormula2

The file references and names are all correct. I'm not sure where in my match formula the issue is...

This is what the sFormula string is:

=INDEX('C:\_Store\10.05.2020\[T MA0 ATP 18.08.2020.xls COMBINED.xls]ATP'!$P$1:$P$126,"X_X_X",1)

And This is what the sFormula2 string is that replaces the "X_X_X" in the Index formula:

MATCH([@Article]&[@Site],('C:\_Store\10.05.2020\[T MA0 ATP 18.08.2020.xls COMBINED.xls]ATP'!$B$1:$B$126)&('C:\_Store\10.05.2020\[T MA0 ATP 18.08.2020.xls COMBINED.xls]ATP'!$D$1:$D$126),0)

Any advice will be greatly appreciated!

the formula must resolve at every step. replace the ""X_X_X"" with 0, then replace that instead. — Scott Craner
Then look here: stackoverflow.com/questions/42492758/… and see if you can redo your formula not to be an array formula. — Scott Craner
Cells(1.1).Replace would replace contents in value of Cells(1,1), it wont edit formula the way you are doing here. Try putting line of sFormula2 before that of sFormula & then in place of sFormula = "=INDEX('" & ref & "'!$P$1:$P$" & rowCountSource & ",""X_X_X"",1)" try sFormula = "=INDEX('" & ref & "'!$P$1:$P$" & rowCountSource & ", sFormula2 ,1)" — Puntal
After that remove or comment out .Replace """X_X_X""", sFormula2 from with block. — Puntal
@Puntal the problem is that .FormulaArray has a 255 character limit and as a work around it must be done in multiple steps to enter one that is longer. — Scott Craner

Puntal Puntal · Accepted Answer · 2020-08-21T20:10:06

Try this code snippet of With block along with some declarations just before it as mentioned.

Dim oldStr As String, newStr As String

    oldStr = """X_X_X"""
    newStr = "sFormula2"

With ws.ListObjects("Table_DCvenSDCdata")
    With .ListColumns("ATP").DataBodyRange
        .NumberFormat = "General"
        With .Cells(1, 1)
            .FormulaArray = sFormula
            .FormulaArray = Replace(.FormulaArray, oldStr, newStr)
        End With
        .FillDown
        .Value = .Value
    End With
End With

Please refer this relevant link.

Excel VBA Array Formula using Index Match giving an error Type mismatch

2 Answers