MMULT in VBA
 

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.

--
Best wishes,

Jim

MMULT in VBA
 

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
--
Best wishes,

Jim


"Jim Jackson" wrote:

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.

--
Best wishes,

Jim

MMULT in VBA
 

On Sun, 30 Jul 2006 08:27:02 -0700, Jim Jackson
wrote:

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
--
Best wishes,

Jim

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
------------------------------------------


--ron

MMULT in VBA
 

Much better.

Thanks.
--
Best wishes,

Jim


"Ron Rosenfeld" wrote:

On Sun, 30 Jul 2006 08:27:02 -0700, Jim Jackson
wrote:

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
--
Best wishes,

Jim

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
------------------------------------------


--ron

MMULT in VBA
 

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


Jim Jackson wrote:
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

MMULT in VBA
 

On Mon, 31 Jul 2006 06:17:02 -0700, Jim Jackson
wrote:

Much better.

Thanks.
--
Best wishes,

Jim

You're welcome. Glad to help.


--ron