Multi variable solver

[quayz15]

I would like to know if what I want to do is even possible. I would like to create a solver that sets one range equal to another range by changing multiple variables.
Specifically, my target cell values for column "E" are currently in column "F"(ft=...) I would like to do this by changing the variables in row "2". Any help is most appreciated. Thank you in advance.


Option Explicit

Private Sub Command1_Click()


Dim s As Integer
Dim xddot As Double
Dim timestep As Integer
Dim z, h As Double
Dim row1, endrow As Integer
Dim time, x, xdot As Double
Dim time1, x1, xdot1 As Double
Dim deltatime, deltaxdot As Double

s = Range(Range("C14"), Range("C65536").End(xlUp)).Count


z = 1
x = 0
xdot = 0
xddot = 0
x1 = 0
xdot1 = 0

endrow = Range("A65536").End(xlUp).ROW

For timestep = 1 To s

time = Range("A14").Offset(timestep - 1, 0).Value
x = Range("B14").Offset(timestep - 1, 0).Value
xdot = Range("C14").Offset(timestep - 1, 0).Value
time1 = Range("A14").Offset(timestep - 2, 0).Value
x1 = Range("B14").Offset(timestep - 2, 0).Value
xdot1 = Range("C14").Offset(timestep - 2, 0).Value
deltatime = time - time1
deltaxdot = xdot - xdot1


On Error Resume Next
xddot = deltaxdot / deltatime
If Err.Number <> 0 Then
xddot = 0
End If

Range("F14").Offset(timestep - 1, 0).Value = xddot

Dim k1, k2, k3, k4 As Double

h = deltaxdot


k1 = h * f(deltaxdot, z)
k2 = h * f(deltaxdot + h / 2, z + k1 / 2)
k3 = h * f(deltaxdot + h / 2, z + k2 / 2)
k4 = h * f(deltaxdot + h, z + k3)
z = z + k1 / 6 + k2 / 3 + k3 / 3 + k4 / 6

Range("G14").Offset(timestep - 1, 0).Value = z

Dim f0 As Double
Dim ft As Double
f0 = -309.6338
ft = fc(z, x, xdot, xddot) - f0
Range("E14").Offset(timestep - 1, 0).Value = ft

Next timestep
End Sub
Function f(deltaxdot, z) As Double
Dim A As Double
Dim n As Double
Dim gamma As Double
Dim betta As Double

A = Range("D2").Value
n = Range("E2").Value
gamma = Range("F2").Value
betta = Range("G2").Value

If z > 0 And deltaxdot > 0 Then
f = A - (gamma + betta) * (z ^ n)
ElseIf z > 0 And deltaxdot < 0 Then
f = A + (gamma - betta) * (z ^ n)
ElseIf z < 0 And deltaxdot < 0 Then
f = A - (gamma + betta) * Abs(z ^ n)
ElseIf z < 0 And deltaxdot > 0 Then
f = A + (gamma - betta) * Abs(z ^ n)
End If

End Function


Function c(xdot) As Double
Dim a1 As Double
Dim a2 As Double
Dim p As Double

a1 = Range("H2").Value
a2 = Range("I2").Value
p = Range("J2").Value

c = a1 * Exp(-(a2 * Abs(xdot)) ^ p)

End Function

Function fc(z, x, xdot, xddot) As Double
Dim alfa As Double
Dim k As Double
Dim m As Double

alfa = Range("K2").Value
k = Range("L2").Value
m = Range("M2").Value

fc = alfa * z + k * x + c(xdot) * xdot + m * xddot

End Function

 

[lameid]

I have a couple questions for you...

Why not just use formulas for the destination range?

What exactly are you trying to do?

I'm guessing that you would find the column and row property of a cell/range usefall as well as the cell method of the sheet object.

 

[quayz15]

My objective is to uses data form a manufacturer which contains time step, displacement, velocity and force. I need to be able to process the displacement and velocity of each time step in order to predict the given force. I need a solver to solve the parameters in which I placed in row 2. I will need to do this for numerous sheets, and apply the parameters to separate program. The reason for the current method is I will need to imbed this program into another, in order to predict a response; however the sole function of the current program is to solve for the parameters. My programming skills are very limited, and I am not aware of many methods of problem solving in VBA.

 

[quayz15]

I'm guessing I can't simple add this after my FOR NEXT loop.

Range("E14:417").Value.GoalSeek _
Goal:=Range("D14417").Value, ChangingCell:=Range("D2:M2")

 

[lameid]

The reason for the current method is I will need to imbed this program into another

Is the whole solution Excel based? If not you probably want to stop thinking about Excel and start thinking in the target application.

 

[lameid]

Looking back at this, I have not even used GoalSeek in Excel... You should record a macro and set it up and look at that code to get a feel for how to progam it.

Also I'm not sure what you are trying to do generically... your code references absolute cell references. Do you want to do it for a range, data in a postition?

To me either this is Excel and you set it up using formulas in the worksheet or it isn't and you might end up with an Excel specific solution by starting there and thinking about specific Excel funtionality like goal seek.

 

[quayz15]

The solutiono is excel based, there are no formulas in the spread sheet. Evedently the parameters are usually
found with "constrained nonliniar optimization using a sequential quadradic programming algorithm in MATLAB." I'm not sure what a sequential quadradic programming algorithm is, and I don't have MATLAB.

The data given in column A B C and D are time, Displacemnt, velocity and Force respectively. I obtain a predicted force though the code per time step column "E". Although the force is calculated every time step the parameters (row 2: alfa gamma betta a1 a2 ect.)must remain constant.

I hope this helps.

 

[quayz15]

Matlab has a tool box that runs an optimization. Is there an Add-in on Excel that does this, or some kind of curve fitting. Basically I need to fit the exerimental force velocity curve with predicted force velocity curve.