Formula Array In VBA

    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

    Re: Formula Array In VBA


    Maybe the formula is too long for VBA to handle as an array formula?


    I split it into 2 separate statements and it gave the results in the 2 separate cells


    Code
    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)))"
ThisWorkbook.Sheets("Home").Cells(42, 18).FormulaArray = "=(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)))"

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Formula Array In VBA


    VBA isn't the culprit.


    You can check, using


    Code
    MsgBox "=(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)))"

    Re: Formula Array In VBA


    When I had it all in 1 statement:


    Code
    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)))"


    I got a FALSE in Q42.... not sure why? But when I split them, I got 2 in Q42 and 1 in R42 (with my sample test).... not sure why....

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Formula Array In VBA


    Quote from NBVC;702258

    When I had it all in 1 statement:


    Code
    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)))"


    I got a FALSE in Q42.... not sure why? But when I split them, I got 2 in Q42 and 1 in R42 (with my sample test).... not sure why....


    Thanks for helping. I did what you suggested by splitting it up, and it worked.


    Now a month later it does not work (it seems to go funny after 10,031 rows of data).... However, if I write the formulae in manually it works again.


    I have no idea why this doesnt work properly!!! Any further ideas?

    Re: Formula Array In VBA


    Seems like an awkward number to break at... hmmm...


    Unfortunately, I am not really a VBA expert and so am not sure if it is a VBA problem or not.


    You might want to describe what you mean by "it goes funny". Do you get wrong result? An error? (if so which one?).


    You might want to see what the "dlastrow" variable is returning (with a debug.print command) to see if it is correct.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Formula Array In VBA


    Did you consider defining a named range to simplify this formula; e.g.


    Code
    columns(8).specialcells(2).offset(1).specialcells(2).name="snb_002"

    Re: Formula Array In VBA


    Thanks for your responses.



    When I said funny, it gives an error of "Unable to set the FormulaArray property of the Range Class" when dlastrow > 10031. If I delete this row, and reset the used range to 10031, the code works. If i set the formulae as text, then remove ' and set as an array on the sheet, it works. I have even tried recording the macro whilst doing this, and though it records fine manually, when i play it bugs with the error above again.


    I have also tried the named range but this doesnt appear to help.


    At worse case I can tell the user to manually do "update" that one cell, but that isnt good enough for me or my own development!

    Re: Formula Array In VBA


    Yeah I was trying to avoid that due to sensitive nature of data. I will remove and send a copy tomorrow. Thanks for taking an interest!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login