Array Formula not executing properly

0

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

Answer
Discuss

Answers

0

try omitting double quotes: Sub ArrayFormla()

    Sheet1.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

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login