MMULT in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having a whale of a time figuring out how to convert the MMULT formula
into VBA. I can do it specifying Ranges but need the code to allow for
activecell.offset values since I have a number of rows that require an MMULT
result.

Is there a way?

Thanks for any help anyone can give.
 
If anyone else comes across this situation, here is the code I finally got to
work:

Sub mmult()

Set r1 = ActiveCell.Offset(0, -8)
Set r8 = ActiveCell.Offset(0, -1)

Set c1 = ActiveCell.Offset(-8, -9)
Set c8 = ActiveCell.Offset(-1, -9)


ActiveCell = "=MMult(" & r1.Address & ":" & r8.Address & "," & c1.Address &
":" & c8.Address & ")"
End Sub


Jim
 
If anyone else comes across this situation, here is the code I finally got to
work:

Sub mmult()

Set r1 = ActiveCell.Offset(0, -8)
Set r8 = ActiveCell.Offset(0, -1)

Set c1 = ActiveCell.Offset(-8, -9)
Set c8 = ActiveCell.Offset(-1, -9)


ActiveCell = "=MMult(" & r1.Address & ":" & r8.Address & "," & c1.Address &
":" & c8.Address & ")"
End Sub


Jim
Click to expand...

Here's another solution:

----------------------------------------
Sub mmult()

Set r1 = ActiveCell.Offset(0, -8)
Set r8 = ActiveCell.Offset(0, -1)

Set c1 = ActiveCell.Offset(-8, -9)
Set c8 = ActiveCell.Offset(-1, -9)

Set Range1 = Range(r1, r8)
Set Range2 = Range(c1, c8)

ActiveCell.Value = Application.WorksheetFunction.mmult(Range1, Range2)

End Sub
 
Much better.

Thanks.
--
Best wishes,

Jim


Ron Rosenfeld said:
Here's another solution:

----------------------------------------
Sub mmult()

Set r1 = ActiveCell.Offset(0, -8)
Set r8 = ActiveCell.Offset(0, -1)

Set c1 = ActiveCell.Offset(-8, -9)
Set c8 = ActiveCell.Offset(-1, -9)

Set Range1 = Range(r1, r8)
Set Range2 = Range(c1, c8)

ActiveCell.Value = Application.WorksheetFunction.mmult(Range1, Range2)

End Sub
Click to expand...
 
Here's just another way:

Sub Demo()
Dim R1 As String
Dim R2 As String
Const n As Long = 8

With ActiveCell
R1 = .Offset(0, -n).Resize(1, n).Address(False, False)
R2 = .Offset(-n, -n - 1).Resize(n).Address(False, False)
.Formula = "=MMult(" & R1 & "," & R2 & ")"
End With
End Sub

Dana DeLouis
 
Back
Top