Optimizing/loop help

[loderdodger]

Hi everyone,
This is a bit related to my last post: http://www.mrexcel.com/forum/showthread.php?p=1991644

What I am trying to do is create a specific "optimizer" type macro for some data I will have.

What I'm working with is a spreadsheet has the $ costs for different projects and an associated Return on Investment (ROI) number for each project. I also will have a budget number for all projects that will not be big enough to pay for all the projects.

So what I would like to do is have a macro that will - look at the Rank of each project (by ROI) and "remove" the project with the lowest ROI, then check to see if the total costs are less than the budget, if not, again look at the lowest ROI and remove that project, then check the budget, and so on, until the costs eventually are within the budget $ amount.

Any suggestions anyone may have on how I could set something like this up, or any specific functions that may make this easier would be greatly appreciated. I just know basic VBA so I'm just figuring this out as I go.

Thanks!

 

[Linus_99]

Hi,

For this type of problem, you could use the Excel Solver function.

Microsoft provides a sample file solvsamp.xls (which I think is packaged with distributions of Excel or you can search for it on the web & download it).

HTH