Using Solver to solve across multiple cells

**Texans57** · 11-19-2012, 03:21 PM

Greetings,

I am having trouble using solver to solve for a target cell across an entire column. For example. cell A1 is calculated with solver with values B1 and C1. Now I want to literally scroll down to A2 where it can be solved using B2 and C2. etc.

I have uploaded the document. Please see comments on document.

If you need any clarification to my request/question, please inform me.

Thanks in advance for any help,
Josh

**MrShorty** · 11-19-2012, 04:26 PM

I'd probably get rid of the constraints in Solver, and add another column F8=D8-A8. Then your Solver routine will set F8 to a value of 0 by changing C8. The reason for this is that, when you get a loop set up to go down the columns, the solver model won't need to change except for the row it is working on.

In order to automate this, you need to use VBA to run a macro that will run Solver down the columns. If you are unfamiliar with macro programming in Excel, start by recording a macro while you manually call Solver and get it to Solve one row for you. That will give you a syntactically correct (even if less than efficient) block of code that will solve a given row. Then you will need to put that block of code into a loop (for...next or other loop) to run down the column.

**Texans57** · 11-19-2012, 05:08 PM

I did as you suggested Mr.Shorty, however I do not know how to do the loop. Can somebody assist me with this? Please take a look at the macro I have setup using the record macro feature.

**MrShorty** · 11-19-2012, 05:57 PM

I do not know how to do the loop.

I would expect a simple For...Next or For Each...Next loop should be able to do this for you. If you are unfamiliar with For...Next loops, put "For statement" into the VBA help search engine.

One thought: If you have a lot of programming to learn, and this is a one time thing, it might be easier (even if it is tedious) to do it manually. Many times I end up spending more time and effort trying to automate a tedious task than I would have if I had just done it the tedious way.

**Texans57** · 11-19-2012, 07:03 PM

Thanks for helping me MrShorty. However this is a one time thing and I would greatly appreciate it if you or someone else could help me with this simple for loop. I have been working on the for loop. I cant seem to get it to work.

I have done some searching and I came across this thread. http://www.excelforum.com/excel-prog...able-help.html

If no one can help me with the loop, then I will just do it manually.

Thanks in advance,
Josh

**jeffreybrown** · 11-19-2012, 07:19 PM

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).