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

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

On Sun, 30 Jul 2006 08:27:02 -0700, Jim Jackson
<[email protected]> 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

Much better.

Thanks.
--
Best wishes,

Jim


"Ron Rosenfeld" wrote:

> On Sun, 30 Jul 2006 08:27:02 -0700, Jim Jackson
> <[email protected]> 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
>

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

On Mon, 31 Jul 2006 06:17:02 -0700, Jim Jackson
<[email protected]> wrote:

>Much better.
>
>Thanks.
>--
>Best wishes,
>
>Jim

You're welcome. Glad to help.


--ron