VVA MMult question

[gt213]

In excel Im using the following array formula in cells D13:D15 {=MMULT(B8:D10,B13:B15)*B13:B15}, and i want to be able to replicate with VBA.

So far i think i have been able to get the first part ok with: Cont = Application.WorksheetFunction.MMult(Range1, Range2)

How do i then multiple this range by Range2, where the multiplication is done element wise?

Thanks

 

[Domenic]

Try...

    Dim x As Variant
    Dim y As Variant
    Dim z As Variant
    Dim i As Long


    x = Application.MMult(Range("B8:D10"), Range("B13:B15"))
    
    y = Range("B13:B15").Value
    
    ReDim z(1 To UBound(x))
    
    For i = LBound(x, 1) To UBound(x, 1)
        z(i) = x(i, 1) * y(i, 1)
    Next i

    Range("D13:D15").Value = Application.Transpose(z)

Or, alternatively...

x = Application.Evaluate("MMULT(B8:D10,B13:B15)*B13:B15")

Hope this helps!

 

[gt213]

Thanks for your help.

I actually ended up using .FormulaArray = "=MMult(Range1, Range2)*Range2"

I'm guessing this could be less efficient than your solution but it is useful for me to have the cells update anytime the values in either range change.

 

[Domenic]

Actually, the only reason I offered those two possible solutions is that in your original post you assigned the result of MMULT() to a variable. So your solution is fine, and might even be more efficient. In any case, I'm glad you found a solution. Thanks for our feedback.

Cheers!