How to loop the solver in the VBA?
Hello guys i desperately need some help!I am working on a very simple formula on solver but i would like to repeat for the next rows and i simply fail every time i try it out!Pls helpppppppppppp!!!!!!!!!!!
This is the main body.I just want it to solve for cells C16,C17,C18 etc with the respective references....again it will be D16:M16 and N16 etc,,,
Please Login or Register to view this content.
Last edited by nchinas; 02-28-2014 at 08:09 PM.
Hi and welcome to the forum
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
I will add the tags for you - this time
Last edited by FDibbins; 02-28-2014 at 07:36 PM. Reason: edited at the request of a senior member
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Perhaps somthing like this would work for you?
This loop starts at row 15 and loops 4 times (till i is 18). If you wish to exstend the range change the stop value of i.Please Login or Register to view this content.
Alf
You are amazing...a million thanks won't be enough!
Glad to be of help and thanks for feed back.
Alf
Alf i introduced what you proposed and it works fine but up to a point.The problem is that i cannot actually spot where the problem lies.If i post the excel file do you think that you can help me?
Sure no problem just also tell me what kind of problem you have and I'll have a go at it.If i post the excel file do you think that you can help me?
Alf
Optimal.xlsmOk i will provide a sort description.The excel file is very simple.It has the returns of 8 stocks the var-covar matrix and the weights.I run the macro as you showed me but solver produces the same values after a point.Now the problem is that as i examine it i come up to the conclusion that it might be a problem of economic nature rather than a calculation problem!Anyway i am looking forward for your reply along with any improvements about VBA!
http://www.youtube.com/watch?v=fa3TG4ZpJY8 i actually tried to reproduce this video!!
I think as you do that this is a maths problem not a solver problem. See my comments on sheet2.
I've also made some slight changes to the macro does not improve anything just makes the macro a bit easier to write. Also made a correction to the line
and changed it toPlease Login or Register to view this content.
You did not have any problem because you that line was written twice in the original macro (my bad really) but you only need to write it once and then you add it just after the "SolverReset" line.Please Login or Register to view this content.
Checked the youtube link but could not hear any comments so I'm a bit baffled how he set it up.
Alf
Alf you did help me a lot!!!!As i told you before i am more than convinced that this is a financial problem and not a calculation one!I did not understand the part with the solver options though....
Yes in the sense that starting from row 35 solver can't meet the constraint A35 = B35 and the rest of the solver result are probably infeasible i.e. rows 36 to 42. You could try to run rows 35 to 42 "manually" and see what solver thinks about them. Problem with running solver in a macro with "UserFinish" and "SolverReset" is that you will not see if solver can't find a solution. You must analyze the result.convinced that this is a financial problem and not a calculation one
Offhand I would guess that the function in B has a max value around 0,62 have you tried to plot the B values in a graph?
Before running solver with a macro you can "open" solver and set the options you wish like "Max time", "Number of iterations" and "Assume non Negtive" and as these settings are not changed by the "SolverReset" command you don't have to add them in the macro.did not understand the part with the solver options though....
Alf
Last edited by Alf; 03-01-2014 at 04:58 PM.
Yes Alf i did a little research...Unless i remove the short selling restrictions it is impossible to get a return higher than that of the individual stock.Again many many thanks for your help!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks