Hello,
I have the following formuale that works when I enter the cell and amend to an array using CTRL+SHIFT+Enter, however I cant seem to chnage the code to automatically arrange as an array.
Code
dlastrow = ActiveSheet.UsedRange.Rows.Count
dLastCol = ActiveSheet.UsedRange.Columns.Count
ThisWorkbook.Sheets("Home").Cells(42, 17).FormulaArray = "=(SUM(IF(FREQUENCY(IF(LEFT(Data!C2:C" & dlastrow & ",8)=""CIBLWORK"",IF(Data!I2:I" & dlastrow & "=""Completed"",MATCH(Data!H2:H" & dlastrow & ",Data!H2:H" & dlastrow & ",0))),ROW(Data!H2:H" & dlastrow & ")-ROW(Data!H2)+1),1)))-(SUM(IF(FREQUENCY(IF(Data!C2:C" & dlastrow & "=""CIBLWORK07"",IF(Data!I2:I" & dlastrow & "=""Completed"",MATCH(Data!H2:H" & dlastrow & ",Data!H2:H" & dlastrow & ",0))),ROW(Data!H2:H" & dlastrow & ")-ROW(Data!H2)+1),1)))"
If I remove the .FormulaArray, and then manually edit and SHIFT+CTRL+ENTER, it works and gives the answer I am looking for)... Is there something I am doing very wrong here?
Mike