How to use Solver to fit multiple columns and keep the results 1

[MrMask]

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.

 

[IRstuff]

So write a macro

TTFN

 

[electricpete]

The following threads all relate in some way to calling solver from a loop or for a range of cells:
thread770-143489
thread770-133864
thread770-41049
thread770-32580

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[MrMask]

I don't know how to use Macros. Besides, at this stage all the calculations need to be transparent so when I transfer to other people they can follow step by step or change the equation for the fitting

rgds

 

[electricpete]

As IRStuff suggsted, maybe a macro could help automate the repretitive parts of the task. You don't need to know VBA to do a macro. It just records your keystrokes and plays them back when you ask it to. Tools/Macro/RecordNewMacro


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[MrMask]

gonna try, thanks

 

[joerd]

You might also look into saving each fitted set as a "scenario".

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[electricpete]

I didn't now about that feature. Would be handy for some stuff I do.
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.

 

[joerd]

It's sort of hidden in the spreadsheet file. Goto Tools/Scenarios and you can manage it from there.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[electricpete]

Cool. Thx joerd.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[MrMask]

I'm getting there folks. thx.

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"?

 

[MrMask]

Hi guys. I figured out to do like this:

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