# VVA MMult question

#### gt213

##### Board Regular
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

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Domenic

##### MrExcel MVP
Try...

Code:
``````    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...

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

Hope this helps!

Last edited:

#### gt213

##### Board Regular

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

##### MrExcel MVP
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!

Replies
1
Views
60
Replies
3
Views
74
Replies
1
Views
61
Replies
9
Views
495
Replies
2
Views
41

1,109,383
Messages
5,528,385
Members
409,817
Latest member
JiNXX9500

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...