[SOLVED] Heading name format not picked up in Match/Index Function with VBA

Heading name format not picked up in Match/Index Function with VBA

I have a heading in a table that starts with "Enter"/loads of spaces, (Enter, "Act", Enter, "Sales) this is what it looks like:


Act

Sales

I'm trying to do an Index/Match with multiple criteria, but it's not picking up that heading. It's raw data that all runs through a macro, so I don't want to manually change it every time.

I've tried recording the formula with a separate macro and just copy that into my code, but it's not working. It works with other headings though.

This is the code that gives an error:

With wsReturns.ListObjects("Table_Waste_Returns")
    .ListColumns("Sales Value").DataBodyRange.Cells(1, 1).FormulaArray = _
     "=INDEX(Table_SAPBW [[ " & Chr(10) & "Act" & Chr(10) & _
      "Sales]] ,MATCH([@[Site Main descr]]&[@Article],Table_SAPBW[Site]&Table_SAPBW[Sales Set Article],0),1)"
End With

And this part seems to be the problem, the heading:

"Table_SAPBW [[ " & Chr(10) & "Act" & Chr(10) & "Sales]]"

It should match 2 criteria and then get a third column's data. As mentioned before it works with other headings, just not this one. I think it's all the spaces in the beginning. Any workaround will be a great help!

Solution

Finally found an answer! Just add VBA. in front of Chr(10). This is my edited and working version:

     "=INDEX(Table_SAPBW [[ " & VBA.Chr(10) & "Act" & VBA.Chr(10) & "Sales]] ,MATCH([@[Site Main descr]]&[@Article],Table_SAPBW[Site]&Table_SAPBW[Sales Set Article],0),1)"