Haizen has to arrange transport for N employees where N can be 1 to 500. There are three types of vehicles available with different capacity and different cost. He wants a formula for the lowest cost combination of vehicles for any number N passengers.
This long episode walks through exploring a brute-force method, then moving to Solver, then using VBA with Solver to solve for all 476 non-trivial cases.
Since the video is fairly long, jump to any part of the video:
Table of Contents
(0:00) Welcome to Use Solver with VBA in Excel
(0:15) Defining the Problem - Transport N employees using lowest cost combination of cars, vans, and bus
(0:50) Filling numbers from 1 to 500 using Fill Series Dialog
(1:38) Using formulas to figure out total cost and capacity of manually selected items
(2:53) Setting up conditional formatting using a formula
(3:35) Setting up a model with input, output, and constraint cells
(3:58) Solver is not in my Data tab of the ribbon
(4:15) Set up Solver the first time
(4:30) Defining a constraint in Solver
(4:48) Specify that some input cells must be integer
(6:24) Re-running Solver for new case
(7:08) Allowing your workbook to have macros
(7:36) Changing Macro Security
(7:51) Recording a Macro
(8:24) Switching to VBA to see the recorded Macro
(8:56) Record Solver Code gives a Compile Error when you run the macro
(9:45) Recap of the manual steps to solve each passenger level
(10:13) Macro to log the Solver results in a lookup table
(11:29) Making Solver Not Display OK After Running with VBA
(13:05) Using a simple loop with GoTo
(14:17) Charting Results to Look for Patterns
(15:30) Making a Copy of the Macro for Single Use
(16:05) Adding a Shape to the Sheet to run the macro
(16:42) Using the Macro to solve a single case for new costs
(17:05) Making a second macro button to run all
(18:32) Wrap-Up
(18:55) Link to Jon Peltier site for more details on using Solver with VBA
This long episode walks through exploring a brute-force method, then moving to Solver, then using VBA with Solver to solve for all 476 non-trivial cases.
Since the video is fairly long, jump to any part of the video:
Table of Contents
(0:00) Welcome to Use Solver with VBA in Excel
(0:15) Defining the Problem - Transport N employees using lowest cost combination of cars, vans, and bus
(0:50) Filling numbers from 1 to 500 using Fill Series Dialog
(1:38) Using formulas to figure out total cost and capacity of manually selected items
(2:53) Setting up conditional formatting using a formula
(3:35) Setting up a model with input, output, and constraint cells
(3:58) Solver is not in my Data tab of the ribbon
(4:15) Set up Solver the first time
(4:30) Defining a constraint in Solver
(4:48) Specify that some input cells must be integer
(6:24) Re-running Solver for new case
(7:08) Allowing your workbook to have macros
(7:36) Changing Macro Security
(7:51) Recording a Macro
(8:24) Switching to VBA to see the recorded Macro
(8:56) Record Solver Code gives a Compile Error when you run the macro
(9:45) Recap of the manual steps to solve each passenger level
(10:13) Macro to log the Solver results in a lookup table
(11:29) Making Solver Not Display OK After Running with VBA
(13:05) Using a simple loop with GoTo
(14:17) Charting Results to Look for Patterns
(15:30) Making a Copy of the Macro for Single Use
(16:05) Adding a Shape to the Sheet to run the macro
(16:42) Using the Macro to solve a single case for new costs
(17:05) Making a second macro button to run all
(18:32) Wrap-Up
(18:55) Link to Jon Peltier site for more details on using Solver with VBA
Transcript of the video:
MrExcel Podcast is Sponsored by Easy-XL.
Learn Excel from MrExcel Podcast, Episode 1830. Use Solver.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question's sent in by Haizen.
Haizen is trying to arrange Transport for a number of employees-- anywhere from 1 employee To 500 employees.
And here's what's available to me-- you can rent a Car for $200 with a capacity of 8, you can rent a Microbus for $230 with a capacity of 14, you can rent a Minibus for $400 with a capacity of 26, and he wants a formula to figure out the right number of Cars, Microbuses, and Minibuses.
Alright, now, let's just pause for a second while I set this up.
First thing I want to do, is I'm going to build a table here from 1 To 500 so that I can see every possible case.
I select this 1, go to the Fill, Series, we're going to Fill in columns from 1 To 500, click OK, and there's our numbers.
Alright, now.
As Haizen said, this is easy for the first 8 People.
We get 1 Car, and then for People 9 through 14, you get 1 Microbus, and then for the number People, 15 through 26, 1 Minibus.
But the 27 is interesting-- 27, you would think you might get 1 Minibus and 1 Car, but that is more expensive than 2 Microbuses.
Alright, so, it's complicated.
Now, let's build a quick formula here to figure out the total Capacity that we've arranged, and the total Cost.
So, H1 Car times H1 Car over here-- I'll press F4-- plus Microbus, times Microbus over here-- Press F4-- plus Minibus, times Microbus over here-- Press F4, that figures our Capacity.
During the pause, I created a similar formula there for cost, and filled in all the blanks with zeros.
We will double-click to shoot this down.
Alright, and my initial reaction is, I need to figure out how this data is working, right?
And, so, I was just going to do it-- just-- horrible, brute force method, and so for 27 People, if we tried 1 Minibus in 1 Car, we've spent $600, but if we tried 2 Microbuses and no Minibuses, $460-- clearly, that's cheaper and, hey, by the way, that holds 28 people.
So, that means all of the answers down to 28 are going to be exactly the same, so I built a little set of formulas here, that will copy the answer above straight down because I don't have to solve all 500 of these problems; I only have to solve when this number here is not enough to cover the number of People I have.
In fact, I can set up Conditional Formatting-- Alt+OD, New Rule, Use a formula, and that formula's going to be based on Cell E29 anytime that =E29<A29, then we will format that in red.
Click OK, Click OK, click OK, and we'll Copy that down.
Alright, so, you know, here we needed to come up with some new solution for 29 People.
But I realized that this brute-force method is just going to be horrible.
So I'm going to insert a second sheet and set up something called Solver.
I'll be right back.
Okay, I'm Back.
Here I am on Sheet 2, I've built this Model where I'm trying to solve for 29 Employees, here's the quantities of Cars, Microbus, and Minibus, and it'll calculate the Capacity and Cost, and I'm going to use an add-in that's been around in Excel for a long time called Solver.
Go out to your Data tab and look on the right-hand side.
If you don't see Solver there, then it's not enabled on your computer, but it's easy to enable-- Alt+TI will open the Add-Ins and you choose Solver Add-in, click Ok.
Alright, see, we now have a solver out here on the right-hand side, we're going to set up a new Solver Parameters, we're going to set F7, the total Cost, to the Min., By Changing these three cells here.
Alright.
But there's some constraints.
First constraint is that this Cell Reference, this Capacity here, has to be greater than or equal to (>=) the 29.
Click OK.
Now, the very first time I did this, earlier today, it quickly solved this, but it solved by saying that we needed some fractional number of Minibuses and that's not the way it works-- you can't have a fractional number of Minibuses.
So, we're going to add some New Constraints that says that this Cell Reference, the number of Cars, must be an integer-- it can't be a fractional number of Cars-- and then the number of Microbuses must be an integer, and then, finally, the number of Minibuses must be an integer.
Now, at this point, this is the last Constraint, so I click OK instead of add-- so, there are my Constraints.
GRG Nonlinear is probably the slowest Solver, but it works more often than not.
Alright, so, I've always used that method, and then, we click Solve.
You can watch down here in the lower left-hand corner as it's running what it's trying to do.
But it comes up with 1 Car and 1 Minibus as the answer, with $600-- Click OK.
And, what's interesting, is that must be the right answer for all of the answers from 29 up through 34, ok?
So, I'm still going to do a little bit of manual effort here-- I'm going to go back to Sheet 1, find 29, which is the first place where we have something in red over here, and say that we need 101.
And we have a cost of $600 and, sure enough, that's good all the way down until we get to row 35.
So back here, 35, Solver, all of the parameters are still there, I just have to click Solve, click OK, and now it's 0, 1, and 1, and that will get us good up until row 40.
I'm going to pause.
Alright, at this point, you know, I'm ready to send this back, but Haizen asked for a formula and so I'm afraid that he has thousands of rows and this really isn't going to be a good way.
If he has thousands of rows, then we just want to do a VLOOKUP into this table.
But we have to finish populating this table which, at this rate, is going to be mind-numbingly boring, alright, because, you know, you would copy those values over and then do 41.
So, I want to see what would happen if I recorded a macro that would handle the Solver part.
So, I'm going to do-- first off, this is xlsx, which is the one horrible Excel format that does not allow macros, how silly is that?-- so, we have to do File, Save as, and we're going to save it, not with an xlsx format, but with xlsm or xlsb-- either one of those is macro-enabled, so we'll click Save.
Alright, so, now, we're allowed to have macros.
Also, if you've never used macros before, Alt+TMS and make sure that you're not set up there on that top one.
The second one is fine-- Disable all macros with notification-- but, don't use the top one because then you won't be allowed to use macros.
Alright, so out here on the View tab, Macros, Record Macro, and-- just-- I'll call it RunSolverOnce-- let's see, Ctrl-- I'm going to put Shift+S in there-- Control+Shift+S for Solve-- Store macro in This Workbook, click OK.
And now I've already keyed 41 in here, I come out to the Data tab, Solver, click Solve, click OK, click Stop Recording, and let's go look at that Code and see what happens.
So, do Alt+F11 to get to VBA, double-click on Modules, double-click on Module1, and sure enough, they are doing things-- it is recording Code, isn't this awesome?
And all of this stuff here-- this SolverOk-- since that's not changing, we just have to do SolverSolve over and over and over again.
This is going to be really good.
Alright, another pause there while I went through and recorded the answer for 41.
Next thing we have to solve is 43, so Alt+F11, RunSolverOnce, we click Run, and we get Compile error, Sub or Function not defined.
Alright, now I have to thank John Peltier, he has a great Website that documents what the problem is here.
We have to go to Tools, References, and Add a Reference to Solver-- and this References includes all open VBA projects, so--we Add that Reference to Solver, and now, when we run this macro-- so we're trying to run for 43-- that one simple change allows it to work.
You see, it's trying to solve the problem down here but we're getting the OK message.
Alright, I'm going to pause again while I record this answer for 43.
Okay, great, I'm back.
Ctrl+Shift+S to run the next one and, boy, it'd be nice if we get rid of this OK, so, we didn't have to click OK-- we could actually have it in some sort of a loop.
Hang on a second-- see, now in the background here, after each time that I run Solver I come back and I key in the right answers and then look for the next spot where they-- we-- don't have enough, and we'll run Solver for 55.
And, so, I kind of have this feeling of what I'm doing manually, and it'd be nice if we could write VBA that would do all of those steps.
For example, it would record the 0, 2 and 1, in the right spot, and then increment this by 1 and add it into Cell C1.
Okay, here's a quick bit of Code that I knocked out.
We're working with two different worksheets-- Sheet1 and Sheet2-- so I declare those as Worksheets.
Dim WS1 as worksheet, Dim WS2 as Worksheet, Set WS1 = Worksheet1, then WS2 = Worksheet2.
This is our recorded Code, SolverSolve, and then I'm going to figure out which row we're running to.
So, if we're working on Employee number 55, that's going to be written to employee-- row-- number 56 because I have one row of headings back here on Sheet1.
And then WS1, on Sheet 1, go to WhichRow, Column2, that's Column B, 1 row, 3 columns wide. is going to be Equal to the value from Row 4, Column 1, Row 5, Column 1, Row 6, Column 1.
So, right there, that is a single line of code with space, underscores as our continuation characters at the end.
And then, figure out which to solve for next.
So, when we solve for 55, this number, here, might be 60, and so we know that everything from 55 to 60 is the same Value and we will want this to be-- this 60 to be-- copied up here as 61.
So, WS2.Cells(1, 3).Value = WS2.Cells(7, 5).Value + 1.
Alright.
And then, we still have the problem that we have to answer OK every time that we run Solver.Solve, but at John Peltier's site, I learned that if I made this into a function, x = SolverSolve, and in parentheses, this user finish here, say (True), means that it will not show us the OK dialog box or the dialog box where we have to hit OK.
Alright, so this is kind of exciting now, because it's about to go solve this one, log the answers for me, and then be ready to run the next one.
So, I'm just anywhere here, I'll click OK, I see that it's down here trying to do its thing and came up with an answer of 0 for 0, which will handle us for employees up to 56, and then it already put the right answer in here of 57.
Let's go check and make sure that it put the right answers back here-- 0 for 0-- so that's the answer for Employees 55 and 56.
It's really kind of funny how sometimes we get a big, huge string, and sometimes it's just a couple before the answer changes.
But, sure enough, row 50-- or-- 57 employees is where it changes again.
Alright, I can keep hitting Ctrl+Shift+FS, Ctrl+Shift+FS, Ctrl+Shift+FS, to have it solve one at a time, but I'm still going to have to be here through lunches, I work on getting this all the way up to 500.
So we'll come back to our Code and right down here at the end, we're going to say, If WS2.Cells(7, 5).Value > 500 Then Exit Sub -- just go ahead and finish-- otherwise (Else), GoTo RunAgain End If.
And RunAgain is just a label that we're going to have up here.
Put in a colon after it, and it says that this will just go ahead and run again.
So, it's going to solve one, log the answers, increment the Value, and then run again, and it's going to keep doing it until we get to 500.
Alright?
So, right now, November 27th, it's 11:02 a.m., I'm going to let this start to run-- Ctrl+Shift+S.
I see down here that it is working on something and then it ups this number from 61 to 67 to 69, and so on.
So, it's going to be running for a while, again I'll pause the video.
Okay, there we are-- 1107.
Took about five or six minutes and we've gotten all the way up where it has solved for every possible combination of-- from-- 1 Passenger up through 500 Passengers.
We know what the answer is.
Now, I'm just curious if there's any sort of a pattern here, so I'm going to let Excel create a chart for me of just the Car, Microbus, and Minibus.
Make sure we're at the top there, see what the Recommended Chart is.
I think it has to be a stacked Bar charts-- that's interesting came up with a Line chart, I'm not going to go with any of those-- I want to see a Stacked Column Chart-- Stacked Column Chart, actually.
So, right here, Stacked Column, click OK.
Yeah, I don't-- okay.
So, I don't see any clear trends there.
Maybe there's a better way to do this to figure out what the trend is.
And is there math that could minimize this?
That's a great question.
I didn't pay enough attention in Math class to know the answer to that.
Some of you watching this on YouTube may say.
"Hey, there's better math," but the dangerous or good thing-- good thing or dangerous thing-- is that, using Solver and a little bit of macro code, I was able to just brute-force run through all 500 possibilities and come up with the correct answers.
Now, what do I do if I'm Haizen?
What if I-- what if, what is my future way to use this?
If I have thousands of different possibilities, I have to solve them all at once, I'm going to do a VLOOKUP into this table.
Alright?
But, if it's a tool, especially where if the cost changes daily, and for each one I have to solve it, well, then I'm probably just going to want a macro that will be RunSolverOnce.
Okay, so, here, let's make this RunSolverAll and then I'm going to copy that Code.
I'm Just going to select from here all the way down to the bottom, Ctrl+C, and RunSolverOnce.
And let's see.
I don't even know if we have to record the answers or not, I'm not going to have it increment, not going to have any of this down there.
So, this little macro now will run Solver one time, and would just be a great tool to use on a go-forward basis to solve one at a time.
So, I'm going to insert a little shape here-- rounded shape-- and we will add a Text box-- "Solve for Cell C1"-- let's do a little Align Center, Align Right, actually hit one extra Return there, increase the size, and then, finally, right-click, Assign Macro, and that's going to be called RunSolverOnce.
And, so, you know, we have a new cost.
So, Today, the cost of a car is 195, the cost of a Microbus is 235, Minibus is still 400, and we have to get 42 Passengers, Solve for Cell C1, that will go out and RunSolverOnce, 030.
So, now we have this great little tool where we're good to go-- that's if we're allowed to do one at a time.
If we really need a formula, well, then, you're going to have to update these costs and have a second button here.
I'm going to Ctrl-click this, Ctrl+drag, and this will be Solve Entire Table, We'll change the macro here, and the colors, so back on Format, choose a different color, and right-click, Assign Macro, RunSolverAll, click ok.
Actually, I'm going to go back to that Code and make sure that we start out with that WS2.Cells(1, 3).Value = 1 and, also, WS1.Cells(3, 2).Resize(499,3).FormulaR1C1 = "=R(-1)C".
That resets everything to be the value just above.
Alright, so, this is actually RunSolverAll.
Alright, so we have two different buttons there-- one to just solve a single time, one to solve the entire table and then our VLOOKUPs would work.
Alright, so we covered an awful lot in this video.
I started out with installing Solver, setting up Solver to run once, using VBA to run the existing Solver over and over and over again, how to use VBA to log the results, using VBA to run all possible-- the entire universe of possibilities-- so that way, we have a table of all of the answers already calculated.
Again, I want to refer you out to John Peltier's site-- peltiertech.com/Excel/SolverVBA.html-- he has great VBA examples there if you need to use Solver.
Like, I was able to run it first, which loads Solver; if you want VBA to run it the first time, you have to look at the code out there.
So, this is, unfortunately, the best answer that I have-- is pre-building this table and then using VLOOKUPS based on the number of Passengers, to get the correct answers.
Probably not-- probably much more convoluted than what Haizen was looking for.
If you know a better way to do this-- you know, think, is there matrix multiplication, is there a way to solve simultaneous equations, is there some other way to solve this without the brute-force method of Solver?
Please, let me know down in the YouTube comments right down.
Alright, hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel Podcast, Episode 1830. Use Solver.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question's sent in by Haizen.
Haizen is trying to arrange Transport for a number of employees-- anywhere from 1 employee To 500 employees.
And here's what's available to me-- you can rent a Car for $200 with a capacity of 8, you can rent a Microbus for $230 with a capacity of 14, you can rent a Minibus for $400 with a capacity of 26, and he wants a formula to figure out the right number of Cars, Microbuses, and Minibuses.
Alright, now, let's just pause for a second while I set this up.
First thing I want to do, is I'm going to build a table here from 1 To 500 so that I can see every possible case.
I select this 1, go to the Fill, Series, we're going to Fill in columns from 1 To 500, click OK, and there's our numbers.
Alright, now.
As Haizen said, this is easy for the first 8 People.
We get 1 Car, and then for People 9 through 14, you get 1 Microbus, and then for the number People, 15 through 26, 1 Minibus.
But the 27 is interesting-- 27, you would think you might get 1 Minibus and 1 Car, but that is more expensive than 2 Microbuses.
Alright, so, it's complicated.
Now, let's build a quick formula here to figure out the total Capacity that we've arranged, and the total Cost.
So, H1 Car times H1 Car over here-- I'll press F4-- plus Microbus, times Microbus over here-- Press F4-- plus Minibus, times Microbus over here-- Press F4, that figures our Capacity.
During the pause, I created a similar formula there for cost, and filled in all the blanks with zeros.
We will double-click to shoot this down.
Alright, and my initial reaction is, I need to figure out how this data is working, right?
And, so, I was just going to do it-- just-- horrible, brute force method, and so for 27 People, if we tried 1 Minibus in 1 Car, we've spent $600, but if we tried 2 Microbuses and no Minibuses, $460-- clearly, that's cheaper and, hey, by the way, that holds 28 people.
So, that means all of the answers down to 28 are going to be exactly the same, so I built a little set of formulas here, that will copy the answer above straight down because I don't have to solve all 500 of these problems; I only have to solve when this number here is not enough to cover the number of People I have.
In fact, I can set up Conditional Formatting-- Alt+OD, New Rule, Use a formula, and that formula's going to be based on Cell E29 anytime that =E29<A29, then we will format that in red.
Click OK, Click OK, click OK, and we'll Copy that down.
Alright, so, you know, here we needed to come up with some new solution for 29 People.
But I realized that this brute-force method is just going to be horrible.
So I'm going to insert a second sheet and set up something called Solver.
I'll be right back.
Okay, I'm Back.
Here I am on Sheet 2, I've built this Model where I'm trying to solve for 29 Employees, here's the quantities of Cars, Microbus, and Minibus, and it'll calculate the Capacity and Cost, and I'm going to use an add-in that's been around in Excel for a long time called Solver.
Go out to your Data tab and look on the right-hand side.
If you don't see Solver there, then it's not enabled on your computer, but it's easy to enable-- Alt+TI will open the Add-Ins and you choose Solver Add-in, click Ok.
Alright, see, we now have a solver out here on the right-hand side, we're going to set up a new Solver Parameters, we're going to set F7, the total Cost, to the Min., By Changing these three cells here.
Alright.
But there's some constraints.
First constraint is that this Cell Reference, this Capacity here, has to be greater than or equal to (>=) the 29.
Click OK.
Now, the very first time I did this, earlier today, it quickly solved this, but it solved by saying that we needed some fractional number of Minibuses and that's not the way it works-- you can't have a fractional number of Minibuses.
So, we're going to add some New Constraints that says that this Cell Reference, the number of Cars, must be an integer-- it can't be a fractional number of Cars-- and then the number of Microbuses must be an integer, and then, finally, the number of Minibuses must be an integer.
Now, at this point, this is the last Constraint, so I click OK instead of add-- so, there are my Constraints.
GRG Nonlinear is probably the slowest Solver, but it works more often than not.
Alright, so, I've always used that method, and then, we click Solve.
You can watch down here in the lower left-hand corner as it's running what it's trying to do.
But it comes up with 1 Car and 1 Minibus as the answer, with $600-- Click OK.
And, what's interesting, is that must be the right answer for all of the answers from 29 up through 34, ok?
So, I'm still going to do a little bit of manual effort here-- I'm going to go back to Sheet 1, find 29, which is the first place where we have something in red over here, and say that we need 101.
And we have a cost of $600 and, sure enough, that's good all the way down until we get to row 35.
So back here, 35, Solver, all of the parameters are still there, I just have to click Solve, click OK, and now it's 0, 1, and 1, and that will get us good up until row 40.
I'm going to pause.
Alright, at this point, you know, I'm ready to send this back, but Haizen asked for a formula and so I'm afraid that he has thousands of rows and this really isn't going to be a good way.
If he has thousands of rows, then we just want to do a VLOOKUP into this table.
But we have to finish populating this table which, at this rate, is going to be mind-numbingly boring, alright, because, you know, you would copy those values over and then do 41.
So, I want to see what would happen if I recorded a macro that would handle the Solver part.
So, I'm going to do-- first off, this is xlsx, which is the one horrible Excel format that does not allow macros, how silly is that?-- so, we have to do File, Save as, and we're going to save it, not with an xlsx format, but with xlsm or xlsb-- either one of those is macro-enabled, so we'll click Save.
Alright, so, now, we're allowed to have macros.
Also, if you've never used macros before, Alt+TMS and make sure that you're not set up there on that top one.
The second one is fine-- Disable all macros with notification-- but, don't use the top one because then you won't be allowed to use macros.
Alright, so out here on the View tab, Macros, Record Macro, and-- just-- I'll call it RunSolverOnce-- let's see, Ctrl-- I'm going to put Shift+S in there-- Control+Shift+S for Solve-- Store macro in This Workbook, click OK.
And now I've already keyed 41 in here, I come out to the Data tab, Solver, click Solve, click OK, click Stop Recording, and let's go look at that Code and see what happens.
So, do Alt+F11 to get to VBA, double-click on Modules, double-click on Module1, and sure enough, they are doing things-- it is recording Code, isn't this awesome?
And all of this stuff here-- this SolverOk-- since that's not changing, we just have to do SolverSolve over and over and over again.
This is going to be really good.
Alright, another pause there while I went through and recorded the answer for 41.
Next thing we have to solve is 43, so Alt+F11, RunSolverOnce, we click Run, and we get Compile error, Sub or Function not defined.
Alright, now I have to thank John Peltier, he has a great Website that documents what the problem is here.
We have to go to Tools, References, and Add a Reference to Solver-- and this References includes all open VBA projects, so--we Add that Reference to Solver, and now, when we run this macro-- so we're trying to run for 43-- that one simple change allows it to work.
You see, it's trying to solve the problem down here but we're getting the OK message.
Alright, I'm going to pause again while I record this answer for 43.
Okay, great, I'm back.
Ctrl+Shift+S to run the next one and, boy, it'd be nice if we get rid of this OK, so, we didn't have to click OK-- we could actually have it in some sort of a loop.
Hang on a second-- see, now in the background here, after each time that I run Solver I come back and I key in the right answers and then look for the next spot where they-- we-- don't have enough, and we'll run Solver for 55.
And, so, I kind of have this feeling of what I'm doing manually, and it'd be nice if we could write VBA that would do all of those steps.
For example, it would record the 0, 2 and 1, in the right spot, and then increment this by 1 and add it into Cell C1.
Okay, here's a quick bit of Code that I knocked out.
We're working with two different worksheets-- Sheet1 and Sheet2-- so I declare those as Worksheets.
Dim WS1 as worksheet, Dim WS2 as Worksheet, Set WS1 = Worksheet1, then WS2 = Worksheet2.
This is our recorded Code, SolverSolve, and then I'm going to figure out which row we're running to.
So, if we're working on Employee number 55, that's going to be written to employee-- row-- number 56 because I have one row of headings back here on Sheet1.
And then WS1, on Sheet 1, go to WhichRow, Column2, that's Column B, 1 row, 3 columns wide. is going to be Equal to the value from Row 4, Column 1, Row 5, Column 1, Row 6, Column 1.
So, right there, that is a single line of code with space, underscores as our continuation characters at the end.
And then, figure out which to solve for next.
So, when we solve for 55, this number, here, might be 60, and so we know that everything from 55 to 60 is the same Value and we will want this to be-- this 60 to be-- copied up here as 61.
So, WS2.Cells(1, 3).Value = WS2.Cells(7, 5).Value + 1.
Alright.
And then, we still have the problem that we have to answer OK every time that we run Solver.Solve, but at John Peltier's site, I learned that if I made this into a function, x = SolverSolve, and in parentheses, this user finish here, say (True), means that it will not show us the OK dialog box or the dialog box where we have to hit OK.
Alright, so this is kind of exciting now, because it's about to go solve this one, log the answers for me, and then be ready to run the next one.
So, I'm just anywhere here, I'll click OK, I see that it's down here trying to do its thing and came up with an answer of 0 for 0, which will handle us for employees up to 56, and then it already put the right answer in here of 57.
Let's go check and make sure that it put the right answers back here-- 0 for 0-- so that's the answer for Employees 55 and 56.
It's really kind of funny how sometimes we get a big, huge string, and sometimes it's just a couple before the answer changes.
But, sure enough, row 50-- or-- 57 employees is where it changes again.
Alright, I can keep hitting Ctrl+Shift+FS, Ctrl+Shift+FS, Ctrl+Shift+FS, to have it solve one at a time, but I'm still going to have to be here through lunches, I work on getting this all the way up to 500.
So we'll come back to our Code and right down here at the end, we're going to say, If WS2.Cells(7, 5).Value > 500 Then Exit Sub -- just go ahead and finish-- otherwise (Else), GoTo RunAgain End If.
And RunAgain is just a label that we're going to have up here.
Put in a colon after it, and it says that this will just go ahead and run again.
So, it's going to solve one, log the answers, increment the Value, and then run again, and it's going to keep doing it until we get to 500.
Alright?
So, right now, November 27th, it's 11:02 a.m., I'm going to let this start to run-- Ctrl+Shift+S.
I see down here that it is working on something and then it ups this number from 61 to 67 to 69, and so on.
So, it's going to be running for a while, again I'll pause the video.
Okay, there we are-- 1107.
Took about five or six minutes and we've gotten all the way up where it has solved for every possible combination of-- from-- 1 Passenger up through 500 Passengers.
We know what the answer is.
Now, I'm just curious if there's any sort of a pattern here, so I'm going to let Excel create a chart for me of just the Car, Microbus, and Minibus.
Make sure we're at the top there, see what the Recommended Chart is.
I think it has to be a stacked Bar charts-- that's interesting came up with a Line chart, I'm not going to go with any of those-- I want to see a Stacked Column Chart-- Stacked Column Chart, actually.
So, right here, Stacked Column, click OK.
Yeah, I don't-- okay.
So, I don't see any clear trends there.
Maybe there's a better way to do this to figure out what the trend is.
And is there math that could minimize this?
That's a great question.
I didn't pay enough attention in Math class to know the answer to that.
Some of you watching this on YouTube may say.
"Hey, there's better math," but the dangerous or good thing-- good thing or dangerous thing-- is that, using Solver and a little bit of macro code, I was able to just brute-force run through all 500 possibilities and come up with the correct answers.
Now, what do I do if I'm Haizen?
What if I-- what if, what is my future way to use this?
If I have thousands of different possibilities, I have to solve them all at once, I'm going to do a VLOOKUP into this table.
Alright?
But, if it's a tool, especially where if the cost changes daily, and for each one I have to solve it, well, then I'm probably just going to want a macro that will be RunSolverOnce.
Okay, so, here, let's make this RunSolverAll and then I'm going to copy that Code.
I'm Just going to select from here all the way down to the bottom, Ctrl+C, and RunSolverOnce.
And let's see.
I don't even know if we have to record the answers or not, I'm not going to have it increment, not going to have any of this down there.
So, this little macro now will run Solver one time, and would just be a great tool to use on a go-forward basis to solve one at a time.
So, I'm going to insert a little shape here-- rounded shape-- and we will add a Text box-- "Solve for Cell C1"-- let's do a little Align Center, Align Right, actually hit one extra Return there, increase the size, and then, finally, right-click, Assign Macro, and that's going to be called RunSolverOnce.
And, so, you know, we have a new cost.
So, Today, the cost of a car is 195, the cost of a Microbus is 235, Minibus is still 400, and we have to get 42 Passengers, Solve for Cell C1, that will go out and RunSolverOnce, 030.
So, now we have this great little tool where we're good to go-- that's if we're allowed to do one at a time.
If we really need a formula, well, then, you're going to have to update these costs and have a second button here.
I'm going to Ctrl-click this, Ctrl+drag, and this will be Solve Entire Table, We'll change the macro here, and the colors, so back on Format, choose a different color, and right-click, Assign Macro, RunSolverAll, click ok.
Actually, I'm going to go back to that Code and make sure that we start out with that WS2.Cells(1, 3).Value = 1 and, also, WS1.Cells(3, 2).Resize(499,3).FormulaR1C1 = "=R(-1)C".
That resets everything to be the value just above.
Alright, so, this is actually RunSolverAll.
Alright, so we have two different buttons there-- one to just solve a single time, one to solve the entire table and then our VLOOKUPs would work.
Alright, so we covered an awful lot in this video.
I started out with installing Solver, setting up Solver to run once, using VBA to run the existing Solver over and over and over again, how to use VBA to log the results, using VBA to run all possible-- the entire universe of possibilities-- so that way, we have a table of all of the answers already calculated.
Again, I want to refer you out to John Peltier's site-- peltiertech.com/Excel/SolverVBA.html-- he has great VBA examples there if you need to use Solver.
Like, I was able to run it first, which loads Solver; if you want VBA to run it the first time, you have to look at the code out there.
So, this is, unfortunately, the best answer that I have-- is pre-building this table and then using VLOOKUPS based on the number of Passengers, to get the correct answers.
Probably not-- probably much more convoluted than what Haizen was looking for.
If you know a better way to do this-- you know, think, is there matrix multiplication, is there a way to solve simultaneous equations, is there some other way to solve this without the brute-force method of Solver?
Please, let me know down in the YouTube comments right down.
Alright, hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
