Sully1440
05-16-2018, 06:04 AM
Hi All,
I'm trying to use the mmult function for a Markov Chain. On my sheet, I've used the formula to get the correct answer in one table. However, I'm trying to use VBA to recreate it in another table just below it using the code below. (See attached sheet). I think I'm really close because the formula is correct for the first pass, but I can't seem to get the data to drag across using the code.
Need Help :(
Thanks,
Jim
Option Explicit
Sub Mmult()
Dim i As Variant
For i = 1 To 15
Cells(18, i + 4) = matrix(1, 1)
Cells(19, i + 4) = matrix(2, 1)
Cells(20, i + 4) = matrix(3, 1)
Next i
End Sub
Function matrix() As Variant ' added As Variant
Dim v As Variant
Dim i As Variant
Dim j As Variant
Dim P(1 To 3, 1 To 3) ' 1 To 3 in both
For j = 1 To 3
For i = 1 To 3
P(i, j) = Cells(i + 4, j + 6)
Next i
Next j
Dim Q(1 To 3, 3 To 3) ' 1 To 1
For i = 1 To 3
Q(i, 3) = Cells(i + 4, 3)
Next i
v = Application.WorksheetFunction.Mmult(P, Q)
matrix = v
End Function
I'm trying to use the mmult function for a Markov Chain. On my sheet, I've used the formula to get the correct answer in one table. However, I'm trying to use VBA to recreate it in another table just below it using the code below. (See attached sheet). I think I'm really close because the formula is correct for the first pass, but I can't seem to get the data to drag across using the code.
Need Help :(
Thanks,
Jim
Option Explicit
Sub Mmult()
Dim i As Variant
For i = 1 To 15
Cells(18, i + 4) = matrix(1, 1)
Cells(19, i + 4) = matrix(2, 1)
Cells(20, i + 4) = matrix(3, 1)
Next i
End Sub
Function matrix() As Variant ' added As Variant
Dim v As Variant
Dim i As Variant
Dim j As Variant
Dim P(1 To 3, 1 To 3) ' 1 To 3 in both
For j = 1 To 3
For i = 1 To 3
P(i, j) = Cells(i + 4, j + 6)
Next i
Next j
Dim Q(1 To 3, 3 To 3) ' 1 To 1
For i = 1 To 3
Q(i, 3) = Cells(i + 4, 3)
Next i
v = Application.WorksheetFunction.Mmult(P, Q)
matrix = v
End Function