Sub EnterSerialNumber()ĪctiveCell.Offset(Counter - 1, 0).Value = Counter
Here is an example that quickly enters serial numbers in all the selected cells. You can also use the For Next loop to go through a collection of objects (such as cells or worksheets or workbooks),
Example 3 – Entering Serial Number in the Selected Cells In such as case, the Counter starts at a higher value and keeps getting decremented by the specified Step value. However, using Step, in this case, is a more efficient way as it does not require the loop to run 10 times, but only 5 times. NOTE: Another way of doing this could be to run the loop from 1 to 10 and within the loop check whether the number is even or odd. So the Count value starts from 2 and then becomes 4, 6, 8 and 10 as the looping occurs.
When you use ‘Step 2’, it tells the code to increment the ‘Count’ value by 2 every time the loop is run. Note that we started the Count value from 2 and also used ‘ Step 2‘. Here is a code that will do it: Sub AddEvenNumbers() To sum the first five even positive integers (i.e, 2,4,6,8, and 10), you need a similar code with a condition to only consider the even numbers and ignore the odd numbers. Example 2 – Adding the first 5 Even Positive Integers So after the first loop, when Counter is 1, ‘Total’ value becomes 1, and after the second loop it becomes 3 (1+2), and so on.Īnd finally, when the loop ends, ‘Total’ variable has the sum of the first 10 positive integers.Ī MsgBox then simply displays the result in a message box. Once it gets into the loop, it holds the total value after every loop. In this code, the value of Total is set to 0 before getting into the For Next loop. It will then display a message box showing the sum of these numbers. Example 1 – Adding the first 10 positive integersīelow is the code that will add the first 10 positive integers using a For Next loop. Let’s have a look at a few examples to better understand how For Next loop works. This counter allows you to run this loop for a required number of times.įor example, if I want to add the first 10 positive integers, then my Counter value would be from 1 to 10. In the For Next loop, you can use a Counter (or any variable) that will be used to run the loop. You specify how many times you want the loop to run and also specify what you want the code to do each time the loop is run.īelow is the syntax of the For Next loop: For Counter = Start To End The same logic is used in the For Next loop in VBA. The ‘For Next’ loop allows you to go through a block of code for the specified number of times.įor example, if I ask you to add the integers from 1 to 10 manually, you would add the first two numbers, then add the third number to the result, then add the fourth number to the result, as so on. Example 3 – Go through All the Cells in a Selection (Highlight negative values).Example 2 – Go through All the Open Workbooks (and Save All).Example 1 – Go through All the Worksheets in a Workbook (and Protect it).Example 2 – Enter Dates For the Current Month.Example 1 – Add First 10 Positive Integers using VBA.
Now to best use loops in Excel VBA, you need to know about the different kinds that exist and the correct syntax of each. Loop through all the charts/objects (and give a border or change the background color).Loop through all the characters in a cell (and extract the numeric part from a string).Loop through all the open workbooks (and save each workbook or close all except the active workbook).Looping through all the worksheets and do something with each (such as protect/unprotect it).Looping through a range of cells and analyzing each cell (highlight cells with a specific text in it).Here are some more practical examples where VBA loops can be useful: In real life, you can do a lot more with VBA loops in Excel that can help you automate tasks. Now this, of course, is very simple of looping in Excel VBA (and you can also do this using conditional formatting). If it turns out to be even, you give it a color, else you leave it as is. You can use a VBA loop to go through the range and analyze each cell row number. Suppose you have a dataset and you want to highlight all the cells in even rows. Here is a simple example of using VBA loops in Excel. You can also perform specific tasks for each loop. In VBA, loops allow you to go through a set of objects/values and analyze it one by one.
To get the most out of Excel and VBA, you need to know how to use loops efficiently.