Runtime error 1004 on MMULT in VBA

[lingkai]

Hi,

Encountering this error on MMULT in VBA - would anyone be able to help me review and point out my mistakes please?

Runtime error 1004: Application-defined or object-defined error

Here's part of the code (pardon the clumsiness):


Range("B7").Select
CriteriaCount = Range(Selection, Selection.End(x1Down)).Rows.Count

'mmult code

Range("F10").Select
Do Until IsEmpty(ActiveCell.Value)

ActiveCell.Offset(0, CriteriaCount + 3).Value = WorksheetFunction.MMult(WorksheetFunction.MInverse(Range(Selection, Selection.End(x1Right))),Range(ActiveCell.Offset(0, 7), ActiveCell.Offset(CriteriaCount, 7)))
ActiveCell.Offset(1, 0.).Select

Loop


Thanks for your help!

 

[Andrew Poulsom]

Welcome to MrExcel.

Does it work if you change x1Right to xlToRight? Note that the second character is a lower case L not the number 1.

 

[lingkai]

Thanks Andrew.

Now I'm getting an error with the MInverse function:

Runtime error 1004: Unable to get the MInverse property of the WorksheetFunction class

 

[Andrew Poulsom]

The MInverse function expects its argument to have an equal number of rows and columns.

 

[lingkai]

I see.

I just tried removing it and a new error occurred...

Unable to get the MMult property of the WorksheetFunction class.

ActiveCell.Offset(0, CriteriaCount + 3).Value = WorksheetFunction.MMult(Range(Selection, Selection.End(xlToRight)), Range(ActiveCell.Offset(0, 7), ActiveCell.Offset(CriteriaCount, 7)))

 

[Andrew Poulsom]

If you put the formula in the cell what would it be?

 

[lingkai]

Tried it, but somehow the formula wouldn't work:

ActiveCell.Offset(0, ColumnCount + 2).FormulaR1C1 = "'=MMULT(F10:" & ColumnLetter(5 + CriteriaCount) & "10,$M$10:$M$" & (9 + CriteriaCount) & ")"


ColumnLetter is a working function that pulls out the correct letter based on CriteriaCount.

This error occurs: Runtime error 1004: Application-defined or object-defined error

Cell F10 is the first of the single-row multi-column matrix.
Cell M10 is the first of the single-column multi-row matrix (same no. of rows as columns in the first matrix)

 

[Andrew Poulsom]

What's ColumnLetter?

 

[lingkai]

Function ColumnLetter(ColumnNumber As Long) As String
Dim n As Long
Dim c As Byte
Dim s As String
n = ColumnNumber
Do
c = ((n - 1) Mod 26)
s = Chr(c + 65) & s
n = (n - c) \ 26
Loop While n > 0
ColumnLetter = s
End Function

 

[Andrew Poulsom]

Does it work if you use Formula rather than FormulaR1C1?