All articles with 'for loop' Tag

Play spreadsheet soccer with Excel Penalty Game [VBA]

Published on Jul 4, 2018 in Charts and Graphs, VBA Macros
Play spreadsheet soccer with Excel Penalty Game [VBA]

We love spreadsheets. And of course, once every four years, we also get mad about soccer. So why not merge both of them in to one awesome, frivolous and fun thing: Introducing….

Excel soccer game

The best part is you don’t have to run up to play this. Set your aim and let RANDBETWEEN() decide your fate.

Continue »

CP048: How to create animated charts in Excel?

CP048: How to create animated charts in Excel?

In the 48th session of Chandoo.org podcast, let’s make some animated charts!!!

What is in this session?

In this podcast,

  • Announcements
  • Why animate your charts?
  • Non-VBA methods to animate charts
    • Excel 2013’s built-in animation effects
    • Iterative formula approach
  • VBA based animation
    • Cartoon film analogy
    • Understanding the VBA part
  • Example animated chart – Sales of a new product
  • Resources and downloads for you
Continue »

CP039: May the FOR Loop be with you – Introduction to For Loops in Excel VBA

Published on Jul 23, 2015 in Chandoo.org Podcast Sessions, VBA Macros

In the 39th session of Chandoo.org podcast, Let’s learn about FOR loops.

There is a special giveaway in this podcast. It is a workbook with several FOR loop VBA code examples. Listen to the episode for instructions.

Introduction to Excel VBA FOR Loops - What are they, how to use them - Chandoo.org Podcast - Session 039

What is in this session?

In this podcast,

  • Announcements
  • What is a loop – plain English & technical definitions
  • For Loop vs. other kind of loops (While & Until)
  • For Next loops
  • For Each loops
  • Nested For loops
  • Special tips on For loops
  • Performance issues & infinite loops
  • Conclusions & giveaway
Continue »

Quickly filter a table by combination of selected cell values using VBA

Published on Jul 15, 2015 in VBA Macros
Quickly filter a table by combination of selected cell values using VBA

Filtering is one of the most used feature in Excel. It is a quick way to take lots of data and narrow down to the subset we want.

But here is one common filtering scenario that is slow as snail.

Imagine you are looking at some sort of sales data (if you can’t imagine, look at the above demo).

Now, you want to filter this list for gender=male, profession=self-employed, product category = chocolates and  quantity = 1.

If you use the right click, filter > filter by selected value approach, this will take several clicks.

Wouldn’t it be cool if you can select the entire combination and say filter?

Unfortunately, no such feature exists in Excel.

But you are not aiming to be ordinary in Excel.  You are aiming to be awesome in Excel. That means, you don’t take no for answer.

Fortunately, we can quickly write a VBA macro that filters a list by selection. So let’s do that.

Continue »

Looking up when data won’t play nice – few more alternatives

Published on Nov 12, 2014 in Learn Excel, VBA Macros
Looking up when data won’t play nice – few more alternatives

Recently, we discussed about the case of unwieldy data and how we lookup what we want using formulas like SUMIFS. Today, let us learn few more ways to solve the same problem.

Suitable structure spawns simple solutions

Poorly structured is the 2nd biggest problem of analysts. The first one is not enough coffee. That is why there is a dictum in the data analytics world.

Structure is everything

So, we can easily solve our lookup problem, if our data were to magically re-arranged in 2 column fashion – Data & Value.

Continue »

Optimization Tips & Techniques for Excel VBA & Macros [Speedy Spreasheet Week]

Published on Mar 22, 2012 in VBA Macros
Optimization Tips & Techniques for Excel VBA & Macros [Speedy Spreasheet Week]

Is VBA slowing down your workbooks? Do you spend hours starting at Excel while the macros finish running? As part of our Speedy Spreadsheet Week, today lets talk about optimization techniques for Excel VBA & Macros.

Optimization Techniques for Excel VBA & Macros

Lets break this in to 2 sections. (1) Quick optimization techniques & tactics (2) Optimization ideas for the long run. Read on…,

Continue »

Putting It All Together – Our First VBA Application [Part 4 of 5 – Excel VBA Crash Course]

Published on Sep 2, 2011 in Automation, VBA Macros
Putting It All Together – Our First VBA Application [Part 4 of 5 – Excel VBA Crash Course]

In part 4 of our VBA Crash Course, we are going to create our very first VBA application using what we learned so far.

We will take the “We Are Nuts” example and create a daily sales tracker application using all the things you have learned so far. You can download the example VBA workbook and play with it to learn better.

Continue »

Understanding Variables, Conditions & Loops in VBA [Part 2 of 5]

Published on Aug 30, 2011 in VBA Macros
Understanding Variables, Conditions & Loops in VBA [Part 2 of 5]

In part 2 of our VBA Crash Course, we are going to learn what Variables, Conditions & Loops are and how to use them in Excel VBA.

What are Variables, Conditions & Loops?

If you are new to computer programming, you might think I am speaking legalese. So, to make it easy to understand, lets assume you run a bunch of stores across the town. To make it colorful, lets call your stores “We are nuts” – a dry fruit and nuts store chain. At the end of every day, you call each of the 24 store managers and ask them how much sales they have made in that day.

Now, you are not the kind of boss who micro-manages & nitpicks. So you don’t really note down sale for every store. Instead, as you call the store manager, you just mentally update the total. So first store says “$2,300” your total is 2300. Second manger says “$4,000”, the total now will be 6300. So on.

The value 6300 here is nothing but a variable.

We will use the “We are nuts” example thru out this lesson & towards the end we will build a sales logging application using Excel VBA.

Continue »