×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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.

RE: How to use Solver to fit multiple columns and keep the results

So write a macro

TTFN



RE: How to use Solver to fit multiple columns and keep the results

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.

RE: How to use Solver to fit multiple columns and keep the results

(OP)
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

RE: How to use Solver to fit multiple columns and keep the results

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.

RE: How to use Solver to fit multiple columns and keep the results

(OP)
gonna try, thanks

RE: How to use Solver to fit multiple columns and keep the results

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.

RE: How to use Solver to fit multiple columns and keep the results

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.

RE: How to use Solver to fit multiple columns and keep the results

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.

RE: How to use Solver to fit multiple columns and keep the results

Cool. Thx joerd.

=====================================
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

(OP)
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"?

RE: How to use Solver to fit multiple columns and keep the results

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources