question still about using array variables in formula

[novice_2010]

Hello, All,

I have following codes:

Sub test()
Dim Array_1 As Variant
Dim Array_2 As Variant


Array_1 = Array(2, 4, 6, 8, 10)
Array_2 = Application.WorksheetFunction.Transpose(Array_1)


Range("A1").FormulaArray = "=mmult(" & Array_1 & "," & Array_2 & ")"

End Sub

As usual, I got a "Type mismatch" error message. How should I re-write the red line of code to make it work?

 

[Andrew Poulsom]

If you want the formula in the cell:

Range("A1").FormulaArray = "=MMULT({1,2,3,4,5,6},{1;2;3;4;5;6})"

If you want the reslut:

Sub T()
    Dim Array_1 As Variant
    Dim Array_2 As Variant
    Array_1 = Array(2, 4, 6, 8, 10)
    Array_2 = Application.WorksheetFunction.Transpose(Array_1)
    Range("A1").Value = WorksheetFunction.MMult(Array_1, Array_2)
End Sub

 

[pgc01]

Hi

If you want A1 to have a formula, not the result of the formula, like:

=MMULT({2,4,6,8,10},TRANSPOSE({2,4,6,8,10}))

Try:

Sub test()
Dim Array_1 As Variant
Dim sArray_1 As String
 
Array_1 = Array(2, 4, 6, 8, 10)
sArray_1 = "{" & Join(Array_1, ",") & "}"
 
Range("A1").FormulaArray = "=mmult(" & sArray_1 & ",transpose(" & sArray_1 & "))"
End Sub

 

[Norie]

One thing I think is wrong is the dimensions of the arrays.

 

[novice_2010]

Thank you.

So there is NO WAY just to put Array Variable Names (which are Array_1, Array_2 in my case) in the formula?

If that is true, it really surprises me, because the following code will work:

Sub test()
Dim Not_Array_1 As Long
Dim Not_Array_2 As Long


Not_Array_1 = 1
Not_Array_2 = 1


Range("A1").Formula = "=sum(" & Not_Array_1 & "," & Not_Array_2 & ")"

End Sub

 

[pgc01]

One thing I think is wrong is the dimensions of the arrays.

Hi Norie

I believe you can always multiply a matrix with its transpose.

 

[novice_2010]

Hi pgc01, thank you for your help (somehow I misread your post initially). your code works perfect for me. http://www.mrexcel.com/forum/member.php?u=61464

 

[Andrew Poulsom]

The variable Array_1 won't be converted to "{2, 4, 6, 8, 10}" which is what MMULT requires.

 

[Norie]

pgc

I know you can.

That wasn't really what I was thinking of.

 

[novice_2010]

thank you all for suggesting.
I think pgc 's suggestion is good for 1-dimensional array; however it won't work for 2-dimensional array. As shown in the following codes:

Option Base 1
Sub test()
Dim Array_1 As Variant, Array_2 As Variant, Matrix As Variant
Dim sMatrix As String

Array_1 = Array(1, 2, 3)
Array_2 = Application.WorksheetFunction.Transpose(Array_1)

Matrix = Application.WorksheetFunction.MMult(Array_2, Array_1)
'Matrix is a 2 dimensional array variable


sMatrix = "{" & Join(Matrix, ",") & "}"

Range("A1").Resize(3, 3).FormulaArray = "=mmult(" & sMatrix & ",transpose(" & sMatrix & "))"
End Sub

How to make the formula in this example work?