Microsoft Excel Tutorial: Using Solver with VBA in Excel: Transporting Employees with Lowest Cost Combination of Vehicles. 🎙️ Join Bill Jelen, aka MrExcel, in today's Netcast as he dives into the powerful world of Solver in Excel. The question, posed by Haizen, involves arranging transport for varying numbers of employees, from 1 to 500, considering different vehicle options with specific capacities and costs. 🚗 Vehicle Options: Car: $200, Capacity: 8 Microbus: $230, Capacity: 14 Minibus: $400, Capacity: 26 🧠 Problem-Solving Approach: 1️⃣ Bill sets up a table to cover all possible cases from 1 to 500 employees. 2️⃣ Using Solver, he iteratively finds the optimal combination of cars, microbuses, and minibuses to minimize costs while meeting capacity requirements. 3️⃣ Solver Parameters are set to consider constraints, such as the total capacity being greater than or equal to the number of employees. 4️⃣ Bill records a macro to automate the Solver process, allowing for efficient analysis of various scenarios. 💡 Insights: Solver is a powerful tool for optimizing solutions in Excel. Macro recording can automate repetitive tasks, saving time and effort. The resulting table provides a comprehensive view of the optimal vehicle arrangement for different employee counts. 🔗 Resources: Solver Add-in: Data tab - Solver (Enable in Alt+TI if not visible) Macro Recording: View tab - Macros - Record Macro Solver VBA Examples: Check out John Peltier's site at 🛠️ Excel Mastery Tip: Use Solver and VBA macros to efficiently solve complex optimization problems and automate repetitive tasks in Excel. Table of Contents (00: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) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: and minibuses arranging transport for employees brute force method capacity car rental conditional formatting. cost calculation Episode 1830 formula for number of cars microbus rental microbuses minibus rental Solver Join the MrExcel Message Board discussion about this video at 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.