Loop and Solver VBA

[Satyaprakash]

I got the VBA for solver from the net. I have worksheets with a large number of rows and columns and I want to use the solver function on every row in a given column. However when I am combining the loop and solver VBA it is not working. Can someone help me out with a VBA that can help me execute a solver function in all occupied cells in the specified column?

 

[NARAYANK991]

Hi Satyaprakash ,

You might find the following thread helpful :

http://chandoo.org/forum/threads/mo...mall-optimization-problems.20427/#post-123161

Narayan

 

[Satyaprakash]

Thanks Narayan. Using That thread I could loop the solver. But I am encountering a different problem now. In my workbook in the sheet Pricing I want to min the column RMSe by changing the column volatility. Once it has done so for one row, the new volatility value after min becomes the value for the next row and is the basis for the next solver minimisation. However when i use the looped code after some 20 or 30 cells all the values in the volatility column stagnate. What is wrong?

 

[NARAYANK991]

Hi Satyaprakash ,

The problem is in the data ; when you find that the solver in VBA is not doing what you think it should be doing , the first step in troubleshooting the problem is to do it manually ; set up the solver manually and run it and see what happens.

Till row 19 on the worksheet , there is no problem ; run the solver with the data on row 19 , and you can see that there is a problem ; code cannot solve this issue. Rectify the data or the solver constraints , and then try.

Use the uploaded file , since I have corrected the code slightly.

Narayan

 

[Satyaprakash]

Thanx Narayan. I had actually done the same thing yesterday and got the correct way. But before I could post it I saw your reply. hanks Anyways You have been a great help.