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 lengthy and informative Episode #1830 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 this video is fairly long, we’ve set up ‘jump points’ so that you may jump to any part of the video:

Table of Contents

  • Defining the Problem – Transport N employees using lowest cost combination of cars, vans, and bus (0:15)
  • Filling numbers from 1 to 500 using Fill Series Dialog (0:50)
  • Using formulas to figure out total cost and capacity of manually selected items (1:38)
  • Setting up conditional formatting using a formula (2:53)
  • Setting up a model with input, output, and constraint cells (3:35)
  • Solver is not in my Data tab of the ribbon (3:58)
  • Set up Solver the first time (4:15)
  • Defining a constraint in Solver (4:30)
  • Specify that some input cells must be integer (4:48)
  • Re-running Solver for new case (6:24)
  • Allowing your workbook to have macros (7:8)
  • Changing Macro Security (7:36)
  • Recording a Macro (7:51)
  • Switching to VBA to see the recorded Macro (8:24)
  • Record Solver Code gives a Compile Error when you run the macro (8:56)
  • Recap of the manual steps to solve each passenger level (9:45)
  • Macro to log the Solver results in a lookup table (10:13)
  • Making Solver Not Display OK After Running with VBA (11:29)
  • Using a simple loop with GoTo (13:5)
  • Charting Results to Look for Patterns (14:17)
  • Making a Copy of the Macro for Single Use (15:30)
  • Adding a Shape to the Sheet to run the macro (16:5)
  • Using the Macro to solve a single case for new costs (16:42)
  • Making a second macro button to run all (17:5)
  • Wrap-Up (18:32)
  • Link to Jon Peltier site for more details on using Solver with VBA (18:55)



…This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill’s book!

“The Learn Excel from MrExcel Podcast Series”

Visit us: MrExcel.com for all of your Microsoft Excel Needs!