Help writing array formulas to VBA array

[keelaboosa]

I've got some VBA code that populates an array with different formulas as text. The array is then written to a table.

    For i = 1 To UBound(array1)
        array1(i, 2) = "=INDEX(tblMCC_4[In],MATCH([@Med],tblMCC_4[Med],0))"
        array1(i, 3) = ""
        array1(i, 4) = ""
        array1(i, 5) = ""
        array1(i, 6) = ""
        array1(i, 7) = "=INDEX(tblGPS,MATCH(Size,tblGPS[Series],0),5)*INDEX(tblMCC_4[% Split],MATCH([@Med],tblMCC_4[Med],0))"
        array1(i, 8) = "=[@BV1]-[@mL1]"
        array1(i, 9) = "=[@BV2]-[@mL2]"
        array1(i, 10) = "=[@BV3]-[@mL3]"
[B]        array1(i, 11) = "=IF([@Med]=""Ni5"",MAX(IF(tblMCC_4[Med]=""Ni5"",tblMCC_4[Slope 1])),SUMPRODUCT(--(tblMCC_4[Med]=[@Med]),tblMCC_4[Slope 1]))"
        array1(i, 12)= "=IF([@Med]=""Ni5"",MAX(IF(tblMCC_4[Med]=""Ni5"",tblMCC_4[Slope 2])),SUMPRODUCT(--(tblMCC_4[Med]=[@Med]),tblMCC_4[Slope 2]))"
        array1(i, 13)= "=IF([@Med]=""Ni5"",MAX(IF(tblMCC_4[Med]=""Ni5"",tblMCC_4[Slope 3])),SUMPRODUCT(--(tblMCC_4[Med]=[@Med]),tblMCC_4[Slope 3]))"
        array1(i, 14)= "=IF([@Med]=""Ni5"",MAX(IF(tblMCC_4[Med]=""Ni5"",tblMCC_4[Slope 4])),SUMPRODUCT(--(tblMCC_4[Med]=[@Med]),tblMCC_4[Slope 4]))"
[/B]        array1(i, 15) = "=IF(MATCH([@Med],[Med],0)<1,0,IF([@AGG1]> 0, ([@BV1]-[@In]) / [@AGG1], ([@BV1]-[@In]) / 0.0000001))"
        array1(i, 16) = "=IF(MATCH([@Med],[Med],0)<2,0,IF([@AGG2]> 0, [@BV2] / [@AGG2],[@BV2] / 0.0000001))"
        array1(i, 17) = "=IF(MATCH([@Med],[Med],0)<3,0,IF([@AGG3]> 0, [@BV3] / [@AGG3],[@BV3] / 0.0000001))"
        array1(i, 18) = "=IF(MATCH([@Med],[Med],0)<4,0,IF([@AGG4]> 0, [@BV4] / [@AGG4],[@BV4] / 0.0000001))"
        array1(i, 19) = "=IF(SUM([HRS1])=0,0,[@AGG1]*MIN([HRS1])+[@In])"
        array1(i, 20) = "=IF(SUM([HRS2])=0,0,[@AGG2]*SMALL([HRS2],COUNTIF([HRS2],0)+1))"
        array1(i, 21) = "=IF(SUM([HRS3])=0,0,[@AGG3]*SMALL([HRS3],COUNTIF([HRS3],0)+1))"
        array1(i, 22) = "=IF(SUM([HRS4])=0,0,[@AGG4]*SMALL([HRS4],COUNTIF([HRS4],0)+1))"
    Next I

The catch is I need the cells in (i,11) thru (i,14) to be array formulas. Is there a clean way to do this?

 

[Eric W]

This is the main idea:

Range("A1").FormulaArray = Array(i, 11)

Adapt as needed.

 

[keelaboosa]

Thanks - that's what I ended up doing. Too bad, though, since it's really slick to just set a range equal to an array and be done with it.