Hi
I'm using an array formula as below where the formula is entered 100% correct on the Macro, however when the the code is excuted the the formula on the Excel sheet disregards a quotation mark at the end of each IF statement (after second $B$1) as seen below with original VBA code and the actual formula excuted in Excel. The data set is a simple row with four numbers, and a fixed number ($B$1). Find attached Excel sheet with example.
Original and correct formula in VBA:
=SUM(IF(C2:F2>$B$1,C2:F2-$B$1,""))/-SUM(IF(C2:F2<$B$1,C2:F2-$B$1,""))
Formula excuted in Excel:
=SUM(IF(C2:F2>$B$1,C2:F2-$B$1,"))/-SUM(IF(C2:F2<$B$1,C2:F2-$B$1,"))
VBA Code:
Sub Multi()
Range("B3").FormulaArray = "=SUM(IF(C2:F2>$B$1,C2:F2-$B$1,""))/-SUM(IF(C2:F2<$B$1,C2:F2-$B$1,""))"
End Sub