Solver in a Loop for multiple rows by changing multiple columns

**glauberpontes** · 04-27-2018, 02:53 PM

Hello guys.

I was having trouble running the Solver in multiple rows. However, I saw in this forum several threats that helped me evolve a lot using VBA (I'm starting my studies now in this language and solved the problem with Solver in a Loop).

I know it must be something simple that I'm missing, however, I cannot do that for two columns at a time (in this case, the P and the R columns).
Here are the spreadsheet and the code.
I would also like to add the R column.

Best Regards,
Gláuber.

Please Login or Register  to view this content.

**Alf** · 04-29-2018, 03:59 AM

Try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Not that the line "Engine:=1, EngineDesc:="GRG Nomlinear" is wrong but it's sufficient to just specify "Engine:=1"

Not sure I understands your logic behind the setting for the object function, i.e. change P and R value to set AI value = AI value?

Alf

Ps Your formula setting on uploaded file was set to "Manual" I would recommend you use "Automatic" when running solver just to be sure values are updated.

**glauberpontes** · 04-29-2018, 07:23 AM

Dear Alf,

it works perfectly! Thank you very much.

Not sure I understands your logic behind the setting for the object function, i.e. change P and R value to set AI value = AI value?

What I would like to optimize was the AI column by changing the P and the R. I must have spelled the code wrong or did not explain well...

As for

Your formula setting on uploaded file was set to "Manual" I would recommend you use "Automatic" when running solver just to be sure values are updated.

Yeah, I had noticed before and made the change. Thanks for the tip.

Best regards,
Gláuber.

**Alf** · 04-29-2018, 08:04 AM

You are welcome and thanks for feedback

What I would like to optimize was the AI column by changing the P and the R.

Well you did that by setting the command "MaxMinVal:=2," i.e. this solver "translates" as sett (AI + i) values to min by changing cells (P + i) and cells (R + i)
and then you specify that cells (AI + i) should be equal to cells (AI + i) using the "SolverAdd CellRef:=Cells(i, "AI").Address, Relation:=2, FormulaText:=Cells(i, "AI").Address"

This is the part I don't understand.

The formula in the cells (AI + i) say in cell AI24

Please Login or Register  to view this content.

refers to cells that have no value in the uploaded file so changing cells (P + i) and cells (R + i) will have no effect when I run the macro. That I have no problem with and it could be that when all these cells have values then then the setting cells (AI + i) = cells (AI + i) make sense.

But if you are happy with the macro modification then I'm happy too.

Alf

Ps If this solves your problem don't forget to mark your thread "Solved" as per Forum rules

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

**Alf** · 04-29-2018, 08:16 AM

Hi sandy666

Thanks for rep, tried to post this on you tab for "Visitor Messages" but as you have removed this I'll post it here and hopefully you will see it.

Alf

**glauberpontes** · 04-29-2018, 11:57 AM

Hello, Alf.

I looked at the spreadsheet and saw that the cells you referred to actually have values.
Anyway, I am uploading another file with the cells now visible, only because I hope it is possible to understand (because I will mark as SOLVED since it helped to understand and solve the problem

).

Gláuber.

**Alf** · 04-29-2018, 12:47 PM

I looked at the spreadsheet and saw that the cells you referred to actually have values.

So right you are! I did not look properly at the column letters so I did not realized you had hidden columns in the first uploaded file. My bad

Alf

**glauberpontes** · 04-30-2018, 01:45 PM

No problem, Alf.
I was really worried that I was mistaken in something on the spreadsheet. But everything went well.

Thanks again for the attention.

Gláuber.

Solver in a Loop for multiple rows by changing multiple columns

LinkBack

Thread Tools

Rate This Thread

Display

Solver in a Loop for multiple rows by changing multiple columns

Re: Solver in a Loop for multiple rows by changing multiple columns

Re: Solver in a Loop for multiple rows by changing multiple columns

Re: Solver in a Loop for multiple rows by changing multiple columns

Re: Solver in a Loop for multiple rows by changing multiple columns

Re: Solver in a Loop for multiple rows by changing multiple columns

Re: Solver in a Loop for multiple rows by changing multiple columns

Re: Solver in a Loop for multiple rows by changing multiple columns

Thread Information

Users Browsing this Thread

Similar Threads

Excel Solver - Multiple By Changing Variables

Search Multiple Columns and Rows - Show results in Multiple columns and rows

Filter - Multiple Columns - Loop through Visible Rows issue

[SOLVED] Using "SOLVER" to match two columns, but how to copy it for multiple rows ??? (URGENT)

Copy multiple columns with varying number of rows into one column on a new sheet and loop

Run Solver Multiple Times (while changing constraints and goal for each iteration)

Changing Multiple Columns into Multiple Rows

Bookmarks

Bookmarks

Posting Permissions