How to use Solver to fit multiple columns and keep the results
How to use Solver to fit multiple columns and keep the results
(OP)
Hello Folks
I'm using Solver to fit data in columns, one at a time to a user-defined function. As the function is the same, the "changing cells" - the constant of my function - are shared for each data column. Therefore, for each data set, the fitted results are changed, so I have to copy and past values to keep the results and use for further analysis.
I could define the constants for each column, but them I'd need to change each function to point to the correct "changing cells".
So far, with a few columns, I could handle, but if I apply the same procedure to a data-set with many columns, it is very time consuming.
If anyone could offer tips or point me to the right direction, it would be great.
I'm using Solver to fit data in columns, one at a time to a user-defined function. As the function is the same, the "changing cells" - the constant of my function - are shared for each data column. Therefore, for each data set, the fitted results are changed, so I have to copy and past values to keep the results and use for further analysis.
I could define the constants for each column, but them I'd need to change each function to point to the correct "changing cells".
So far, with a few columns, I could handle, but if I apply the same procedure to a data-set with many columns, it is very time consuming.
If anyone could offer tips or point me to the right direction, it would be great.





RE: How to use Solver to fit multiple columns and keep the results
TTFN
RE: How to use Solver to fit multiple columns and keep the results
thread770-143489
thread770-133864
thread770-41049
thread770-32580
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: How to use Solver to fit multiple columns and keep the results
rgds
RE: How to use Solver to fit multiple columns and keep the results
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: How to use Solver to fit multiple columns and keep the results
RE: How to use Solver to fit multiple columns and keep the results
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: How to use Solver to fit multiple columns and keep the results
I tried that out and after I solved it prompted me for a name to save the scenario. I gave it a name but I'm not sure where the scenario went to. Saved as a file somewhere? Or hidden within the spreadsheet?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: How to use Solver to fit multiple columns and keep the results
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: How to use Solver to fit multiple columns and keep the results
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: How to use Solver to fit multiple columns and keep the results
Now I need to copy and paste - text only - the results from each column. I'm using a subroutine for each fitting it got posted by joerd in dec'05 (thanks joerd, it works pretty well):
Sub EthBal3()
Dim C As Range
For Each C in [$AF$5:$AF$8]
SolverOk SetCell:=C.Address, MaxMinVal:=3, ValueOf:="0", ByChange:=C.Offset(0,-5).Address
SolverSolve Userfinish:=True
Next C
End Sub
So how to copy each range of value for each column as referred by each "C"?
RE: How to use Solver to fit multiple columns and keep the results
Dim counter As Integer
For counter = 2 To 13
SolverOk SetCell:=Cells(66, counter).Address, MaxMinVal:=3, ValueOf:="0", ByChange:="$C$2:$C$5"
SolverSolve Userfinish:=True
ActiveSheet.Range("B69:B85").Copy
ActiveSheet.Cells(90, counter).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next counter
maybe not the most elegant way but it works fine, so far.
Need to go to VBA class in a hurry