how to include array variable in the formula?

[novice_2010]

I have following codes:

Sub test()
Dim ArrayVariable As Variant

Range("A1").Resize(1, 5).Name = "Range_Name_1"
Range("A5").Resize(5, 1).Name = "Range_Name_2"

Range("G1").Formula = "=mmult(Range_Name_1, Range_Name_2)"
Range("G2").FormulaArray = "=mmult(Range_Name_1, transpose(Range_Name_1))"

ArrayVariable = Range("A5").Resize(5, 1).Value
Range("J1").FormulaArray = "=mmult(Range_Name_1," & ArrayVariable & ")"

End Sub

I got a Type Mismatch error for the line in red. How to re-write this line of code? (ArrayVariable is a variable that contains a 5 by 1 array)

 

[repairman615]

Hello,

Not real sure here. Try to use a msgbox arrayvariable. This could shed some light on what the arrayvariable is representing.

Also try .address instead of .value in the line above the red line.

-Jeff

 

[novice_2010]

thank you very much. I followed your recommendation. Now the code works:

Sub test()
Dim ArrayVariable As Variant

Range("A1").Resize(1, 5).Name = "Range_Name_1"
Range("A5").Resize(5, 1).Name = "Range_Name_2"

Range("G1").Formula = "=mmult(Range_Name_1, Range_Name_2)"
Range("G2").FormulaArray = "=mmult(Range_Name_1, transpose(Range_Name_1))"

ArrayVariable = Range("A5").Resize(5, 1).Address
Range("J1").FormulaArray = "=mmult(Range_Name_1," & ArrayVariable & ")"

End Sub

Thanks again.

 

[repairman615]

Your welcome. Glad to help.

-Jeff

 

[mikerickson]

Alternatly you could us the expression

"{" & Join(arrayVariable, ";") & "}"