Arithmetic Operation for Variant Data Type in VBA

Question


Public Sub MMULT()

Dim temp As Variant
Dim total As Double
Dim a, b As Range
Dim i As Integer

total = 0.45

For i = 1 To 9
Set a = Range(Cells(i + 15, 3), Cells(i + 15, 11))
Set b = Range(Cells(16, 14), Cells(24, 14))
temp = Application.WorksheetFunction.MMULT(a, b)
total = total + temp
Next i

Range("M9").Select
Range("M9").Value = total

End Sub

Hi, can someone help me to overcome the error? i got a message box "Type mismatch" in line 14 that contain the code: total = total + temp. i have tried several changes like turn all the data type of variables in to variant, but it's still give the same error. Thanks

Why are you using a Variant? You will need to check the variant to see if it is numeric before you try to do math on it... docs.microsoft.com/en-us/office/vba/language/reference/… — braX

Tim Williams Tim Williams · Accepted Answer · 2020-11-17T06:38:19

MMULT returns an array (even when only a single value is returned) - you can't add a Double and an array.

total = total + temp(1)

Arithmetic Operation for Variant Data Type in VBA

1 Answers