PDA

View Full Version : Vba code for solving N equations N unknowns



Milade8080
03-15-2014, 03:21 AM
hi
solving N equations N unknowns in vba With the following conditions:
Example
a11X1 +a12X2+……+a19X9 = b1
a21X1 +a22X2+……+a29X9 = b2
. . . . . . . . . . . . .
a91X1 +a92X2+……+a99X9 = b9
in Sheet 1
a11 in Cell A1 and a12 in Cell B1 ...
a21 in Cell A2 and a22 in Cell B2 ...
.....
.....


in Sheet 2
b1 in Cell A1
b2 in Cell A2
.....
.....

in Sheet 3 (An important part of my question)
solving N equations N unknowns Without using any formula, just press button key(vba code)
---------------------------------------------
Tanks

SamT
03-15-2014, 07:30 AM
In sheet1 you have 9 columns and 89 rows of values for "a"
In sheet2 you have 1 column and 9 rows of values for "b"

You don't know what you want in sheet 3 because you show that you want to solve for 9 values of X in each equation and there are almost an infinity of possible values for each X in each equation. One example:
X1 = b1 /a11 where X2 through X9 = 0

By the way, you will have to show us the equation with X on the left hand side of the "=" sign and "a" and "b' on the right hand side:
X = ???

Also BTW, your question will be much easier to understand if you use Excel cell and math notation in the formula

A1*X + B1*X + C1*X... + I1*X = J1
Where "b1 - b9"" is in column "J"

mikerickson
03-16-2014, 11:13 PM
Select Sheet3!A1:A9

Type =MMULT(MINVERSE(Sheet1!$A$1:$I$9), Sheet2!$A$1:$A9$)

Press Ctrl-Shift-Enter (Cmd+Return for Mac)

If a solution to the system of equations exists, it will be shown.

Any VBA routine would be slower and less robust than native Excel.

mikerickson
03-17-2014, 01:08 PM
Here is a VBA version that uses the worksheet functions.

Sub test()
Dim aArray As Variant
Dim bArray As Variant

Dim res As Variant
aArray = Sheet1.Range("A1:I9").Value
bArray = Sheet2.Range("a1:a9").Value

res = WorksheetFunction.MInverse(aArray)
res = WorksheetFunction.MMult(res, bArray)
With Sheet3.Range("A1")
.Resize(UBound(res, 1), UBound(res, 2)).Value = res
End With
End Sub