Macro copy one row at a time

Hi to all

I'm new with vba programming so let me explain what's my problem. I would really appreciate if someone can help me.
I have many combinations located in spreadsheet ''COMB'' in cell from b1:f1 in 792 rows. I would like to copy each row at a time to the spreadsheets ''WGP'' in cell from b4:b8 and then run my existing macro which run excel solver and then copy results to the b48 and bellow

All I need is a code that will automatically copy one row at a time, run solver and copy results to another location and then copy next row and so on till end of a list of rows


I attach my excel file and existing macros which was run manual by button, but now I want to automatic run all by some kind of a loop.
Thanks in advance. Josef


Excel solver code

Please Login or Register  to view this content.

copy in two row based on number of scenario

Please Login or Register  to view this content.

now i have a button which move manualy to the next scenario number

Please Login or Register  to view this content.

That's my goals, to copy each row at a time, run a solution via solver and copy to another location and go for next row and make same process, so late I can find best option from all 792 combinations.

In cell D4:G8 I have function which one show numbers based by name of the color in B4:B8.
And then excel solver make solution by these numbers and put it in cell I4:I8, other macro then copy name of the colors from B4:B8 to B48:F48 and solution from solver from I4:I8 too B49:F49

but if it's easy to copy in a new sheet, or in a different order or any other way i'm acceptable for it

I think it's gonna work, but when i run macro i got this message from solver and i cannot close it.
When I force program to close I receive another message box from solver password ask me enter password ?

Actually I can't say if it's working. Something is copying but even if I reduce that list in COMB worksheet to few I cant press button in that solver box till end, it shows over and over. I have to force close whole program.
There are also missing values in F48 row and bellow


solver.jpg

I also miss code to copying results from I4:I8 to row bellow

I have to appologize i was urgently absent these days

This is weighted goal programming model so in minimization cell K3 i have this function

Formula:

Please Login or Register  to view this content.

I4:I8 are changing cells and whole list of constraints are written below

Please Login or Register  to view this content.

I didn't post the whole model, because i didn't want to make a mess, but if would be easy i will. I made a print screen how this model look like


print.jpg

I think it would be better if you uploaded the whole model.

I'm guessimating you wish to run Solver in a loop. That requires a setting to Solver in Visual Basic -> Tools -> References and tick box marked Solver. Do you know the complexity of you calculations.

The default setting for Solver is "GRG nonlinear" and "smooth" problems but it your problem is linear then the "Simplex LP" runs faster. The Evolutionary engine is for non "smooth" problems. Don't know much about this.

Looping Solver it is a good idea at the start to add the line

Please Login or Register  to view this content.

to avoid buildup of the criteria you are setting.
To get the result and prepairing Solver for the next run you can add the line

Please Login or Register  to view this content.

Alf

Looing at you model I can only see you have defined 8 different wines i.e. the one found in sheet "Base" under heading "Red", "Pink" and "yellow" so pasting values from sheet"COMB" makes most values in D4:G8 "#N/A"

Running solver with your original macro works and testing with selecting different wines from the dropdown menu will not generate a solution as solver can't find a point that meets with all constraints so I think you need to relax these a bit.

Running Solver in a loop if your rows go from 1 to 792 could be done with a macro like this

Please Login or Register  to view this content.

Alf

Thanks for help,

reason why most values are D4:G8 "#N/A" is because I use this code from Myrna Larson also posted on forum which generate all possible combinations, but in some reason it makes a space bar before every word in column B,C,D and E so words from sheet ''COMB'' and that in data validation list are not the same. Can you check code below ?

Please Login or Register  to view this content.

I know i ask silly questions but i'm a real amateur with VBA and I use codes only if can be easily modified. In your code you write to enter my other constraints, but i don't know how, everything i write into is in red. Here are my constraints

Please Login or Register  to view this content.

Can I ask you for help to modify my old code and add it to your code. Code have to copy all calculated results from solver to the new sheet. Now i used this code also with combination of other button which moves number to the next one. I just need to copy every result from all scenarios placed in ("B4:B8") ("I4:I8") and ("c14:c18") to another location
Now i was copying each scenario in two rows, but it would be easy to copy only in one row.

Please Login or Register  to view this content.

The easy part, vba code with your solver contrain settings.

Please Login or Register  to view this content.

The easiest way to write a code like this is:

Open solver, clear all data from solver by pressing button "Reset All" click "ok" and close solver.

The go to "Developers" tab and click "Record Macro"

Start doing all steps to run solver i.e. click on data tab and fill in all the values you normaly do and run solver if you like. Close solver and go back to the "Developers" tab and click "Stop Recording".

You now have a macro recording of the solver set up. You don't get the extras like

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and you also get a second setup of line

Please Login or Register  to view this content.

before the "SolverSolve" line, but you don't need this so delete it as it makes code more easy to read.

Will have a go at you other questions ans see if i can do something there as well.

Alf

Looking at you copy and paste code it could be simplified

Please Login or Register  to view this content.

Not sure of your prameter "stc" is this a counter that increases after each copy or? Because at the moment it looks to me that old data will constantly being "ower written" by new data
Since you aim to run solver in a loop 792 times you will get 792 lines with values and you take 5 values from a column and transposes it to a row so the first result gets written to the B column, C column, D column, E column and F column.

The next values gets written to A, B, C, D and E column and the last set will be written to F, G, H, I and J column. You sure you can keep all this values appart?

I would think a separate worksheet for the result would be easier to read.

Alf

thanks for help, I forget that record a macro was also an option to add my constraints - THANKS!

''stc'' was a counter. In my previus model i write a macro which was powered manualy by button. and that button move scenario number to the nextone so other button whichone copy data can read that number and copy to certain location and dont owerwrite the number as now i think it's happen.

In my previous model i also copy data in two rows, but now it would be easy to put all result data in one row and new worksheet better asswell.
but now macro have to automatically copy results in B4:B8; I4:I8;C14:C18 to rows one by one. I think the best way to do this is create new worksheet and copy all results in rows in order from 1-792

So result from B4:B8 have to be written in new worksheet cell A1:E1 ; I4:I8 in cell F1:J1 ;C14:C18 in cell K1:O1 and next result in 2nd row and so on.

Yes that sounds resonable. If I assume the new sheet is named "Result" then I would suggest a code like this and by the way I'm assuming you run Solver in a loop.

Please Login or Register  to view this content.

The "Next" is there to return the loop back to reading the next line from "COMB" sheet and start another Solver run.

Still I'm not sure how you set the solver loop i.e. reading in the lines you wish to "feed" to solver.

Re troubleshooting the Myrna Larson permutation code my excel knowhow is not up to that. Sorry

Alf

It works, thanks !

now I only have to change Myrna Larson code.

Thanks' a lot !

You are welcome and thanks for the feedback.

By the way if the problem with Myrna Larson's code is that

but in some reason it makes a space bar before every word in column B,C,D and E

you could use a macro to "clean" the permuted result.

As pressing the space bar makes a blank you could use the "TRIM" function to get rid of it i.e.

Please Login or Register  to view this content.

Alf

it works, thanks