Trouble creating a macro for multiplying matrices

[L1meta]

Hi,

In this sheet I have a matrix of values indexed from 1 to 30, and its transpose to the right.

Below the first matrix I have created the product of the two matrices. I want to create 98 of those. I tried recording a macro that I could edit to automate the process, but I'm not able to change the parameters for the MMULT function in an appropriate fashion.

The procedure was supposed to be like this:
Productmatrix 1 (in the sheet) : First column of transpose * First row of matrix

Productmatrix 2: Second column of transpose * second row of matrix
etc..

Can anyone help me?

SampleMatrix.xlsx

[Bob Phillips]

I have already answered this, so what was wrong with that?

[L1meta]

Thank you for your reply

This one is slightly different, and I'm not yet skilled enough to be able to edit the previous code to make it do what I want.

[Bob Phillips]

Well, I think it would be helpful to spell out what is different between the two, and give the code from the previous solution.

[L1meta]

I realized now that the topic title of my thread might not have been apropriate. This was the previous code, which worked well for the previous task.

[VBA]
Public Sub CreateMatrices()
Const FORMULA_COUNT As String = _
"SUMPRODUCT(--(TEXT(C3:C<lastrow>,""yyyymmm"")=""<testdate>""))"
Dim LastRow As Long
Dim LastCol As Long
Dim NextDate As Date
Dim NextRow As Long
Dim vecResult As Variant
Dim sh As Worksheet

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
LastCol = .Range("D2").End(xlToRight).Column
NextRow = 3
Do

NextDate = .Cells(NextRow, "C").Value
numrows = .Evaluate(Replace(Replace(FORMULA_COUNT, _
"<lastrow>", LastRow), _
"<testdate>", Format(.Cells(NextRow, "C").Value, "yyyymmm")))
vecResult = VarCovar(.Cells(NextRow, "D").Resize(LastRow - NextRow + 1, LastCol - 3))
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
sh.Name = Format(.Cells(NextRow, "C").Value, "yyyymmm")
sh.Range("A1").Resize(numrows, LastCol - 3) = vecResult

NextRow = NextRow + numrows
Loop Until NextRow > LastRow
End With

Application.ScreenUpdating = True
End Sub

Function VarCovar(Rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer

numcols = Rng.Columns.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
For i = 1 To numcols
For j = 1 To numcols
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(Rng.Columns(i), Rng.Columns(j))
Next j
Next i

VarCovar = matrix
End Function [/VBA]

The set that this code was designed for was a 30*2296 array of returns, for which it would provide covariance matrixes. And it creates new sheets for each matrix.

My current problem contains a 98*30 array of beta coefficients for the assets(30) during all the months(98) of the previous dataset, plus its transpose. I want to be able to create 98 matrices by doing MMULT(Column i of transpose;Row i of array). And preferably all in one sheet with some space between them. Does that make sense?

[Bob Phillips]

In your example workbook, you have 2 @ 98x30 arrays, one above the other, and 1 @ 30x98 array. Which should we be working with in this case?

[L1meta]

Array1: B3:AE100 is the array of Betas

Array2: AH2:EA32 is the transpose of Array1

Array3: B102:AE131 is an example of what I want 98 of, which is the columns of Array2 multiplied by rows of Array1

Thank you for taking time to help me with this