Скачать или смотреть Excel - Step by 0.01: Episode 1350

Microsoft Excel Tutorial: Solving Loop Problems in Excel: Excel Scenario Manager and VBA For Next Loop | MrExcel Podcast.

Welcome to another episode of the MrExcel Podcast. In this episode, we will be discussing a common issue that many Excel users face when using the scenario manager. As part of my new and improved scenario manager, I wanted to create an input screen where users could easily run multiple scenarios with different variables. However, I ran into a problem when using a “For Next” loop in VBA. Let’s dive into the details and see how we can solve this issue.

First, let’s talk about the “For Next” loop in VBA. If you’re not familiar with macros, don’t worry, I’ll keep it simple. When using a “For Next” loop, the loop will run once for each value in the specified range, with a specified step. For example, if we have a range from 4 to 8 with a step of 2, the loop will run once for 4, once for 6, and once for 8. However, after the loop, the value will be at 10, which can cause problems when trying to run multiple scenarios.

Now, let’s get into the issue I faced with the scenario manager. I wanted to run a loop from 5% to 6% with a step of 1%, which should have given me two scenarios, one at 5% and one at 6%. However, when I ran the macro, it only gave me the 5% scenario. After some investigation, I realized that this was due to the floating-point problem in Excel. This means that while computers store numbers as binary, which doesn’t convert well to tenths, the 0.5 or 0.6 in our case is a repeating number in binary and can never be exactly equal. This is where the “Dim i As Currency” variable type comes in.

By using the “Dim i As Currency” variable type, we can solve the floating-point problem and get the desired results. Currency is a special type in Excel that has four decimal places, giving us an accuracy of up to 0.0001. This allows us to run the loop with small steps, like 1%, without any issues. So, if you’re facing a similar problem with loops in Excel, give the “Dim i As Currency” variable type a try and see if it solves your issue.

I hope this tip helps you in your Excel journey. Thank you for tuning in to this episode of the MrExcel Podcast. Don’t forget to subscribe to our channel and hit the notification bell to stay updated with our latest videos. See you next time for another informative netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

Table of Contents:
(00:00) VBA Loop Step by 0.001 does not finish
(00:18) Input screen for running scenarios
(00:38) Solution using “Dim i As Currency”
(01:08) Testing the scenario manager
(01:26) Floating-point problem in Excel
(02:21) Tip from MrExcel message board
(02:46) 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:
1% increments in loops
Binary conversion
Excel currency variable type
Excel scenario manager
Floating-point problem
Loop accuracy
MrExcel Podcast episode 1350
Small steps in loops
Solving loop problems in Excel
Step 0.01
VBA For Next loop


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...


Today, in Episode #1350, Bill examines an Excel VBA [Visual Basic for Applications] loop is supposed to step by 1% increments (0.01) - but it isn't working. Bill shows us the solution to get the result intended. Learn Excel 97-2007 from MrExcel.