For to step vba

For to step vba DEFAULT

Named Arguments

No

Syntax

For = To [Step 'code to execute on each iteration[Exit For] Next []

Use: Required (optional with statement)

Data Type: Numeric Variant

Any valid numeric variable to be used as the loop counter.

Use: Required

Data Type: Numeric Variant

Any valid numeric expression that specifies the loop counter's initial value.

Use: Required

Data Type: Numeric Variant

Any valid numeric expression that specifies the loop counter's maximum value.

Use: Optional (required if used)

Data Type: Numeric Variant

Any valid numeric expression that indicates how much the loop counter should be incremented with each new iteration of the loop.

Description

Defines a loop that executes a given number of times, as determined by a loop counter. To use the loop, you must assign a numeric value to a counter variable. This counter is either incremented or decremented automatically with each iteration of the loop. In the statement, you specify the value that is to be assigned to the counter initially and the maximum value the counter will reach for the block of code to be executed. The statement marks the end of the block of code that is to execute repeatedly, and also serves as a kind of flag that indicates the counter variable is to be modified.

Rules at a Glance

  • If is less than , and no keyword is used ...

Sours: https://www.oreilly.com/library/view/vb-vba/1565923588/1565923588_ch07-1091-fm2xml.html

Excel VBA Loops: For Next, Do While, Do Until, For Each (with Examples)

To get the most out of Excel and VBA, you need to know how to use loops efficiently.

In VBA, loops allow you to go through a set of objects/values and analyze it one by one. You can also perform specific tasks for each loop.

Here is a simple example of using VBA loops in Excel.

Suppose you have a dataset and you want to highlight all the cells in even rows. You can use a VBA loop to go through the range and analyze each cell row number. If it turns out to be even, you give it a color, else you leave it as is.

Now this, of course, is very simple of looping in Excel VBA (and you can also do this using conditional formatting).

In real life, you can do a lot more with VBA loops in Excel that can help you automate tasks.

Here are some more practical examples where VBA loops can be useful:

  • Looping through a range of cells and analyzing each cell (highlight cells with a specific text in it).
  • Looping through all the worksheets and do something with each (such as protect/unprotect it).
  • Loop through all the open workbooks (and save each workbook or close all except the active workbook).
  • Loop through all the characters in a cell (and extract the numeric part from a string).
  • Loop through all the values an array.
  • Loop through all the charts/objects (and give a border or change the background color).

Now to best use loops in Excel VBA, you need to know about the different kinds that exist and the correct syntax of each.

Using Loops in Excel VBA - The Ultimate Guide

In this tutorial, I’ll showcase different types of Excel VBA loops and cover a few examples for each loop

Note: This is going to be a huge tutorial, where I will try and cover each VBA loop in some detail. I recommend you bookmark this page for future reference.

If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training.

For Next Loop

The ‘For Next’ loop allows you to go through a block of code for the specified number of times.

For 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..

Isn’t it?

The same logic is used in the For Next loop in VBA.

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.

Below is the syntax of the For Next loop:

For Counter = Start To End [Step Value] [Code Block to Execute] Next [counter]

In the For Next loop, you can use a Counter (or any variable) that will be used to run the loop. This counter allows you to run this loop for a required number of times.

For example, if I want to add the first 10 positive integers, then my Counter value would be from 1 to 10.

Let’s have a look at a few examples to better understand how For Next loop works.

Example 1 – Adding the first 10 positive integers

Below is the code that will add the first 10 positive integers using a For Next loop.

It will then display a message box showing the sum of these numbers.

Sub AddNumbers() Dim Total As Integer Dim Count As Integer Total = 0 For Count = 1 To 10 Total = Total + Count Next Count MsgBox Total End Sub

In this code, the value of Total is set to 0 before getting into the For Next loop.

Once it gets into the loop, it holds the total value after every loop. 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.

And finally, when the loop ends, ‘Total’ variable has the sum of the first 10 positive integers.

A MsgBox then simply displays the result in a message box.

Example 2 – Adding the first 5 Even Positive Integers

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.

Here is a code that will do it:

Sub AddEvenNumbers() Dim Total As Integer Dim Count As Integer Total = 0 For Count = 2 To 10 Step 2 Total = Total + Count Next Count MsgBox Total End Sub

Note that we started the Count value from 2 and also used ‘Step 2‘.

When you use ‘Step 2’, it tells the code to increment the ‘Count’ value by 2 every time the loop is run.

So the Count value starts from 2 and then becomes 4, 6, 8 and 10 as the looping occurs.

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. 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.

The Step value can also be negative. In such as case, the Counter starts at a higher value and keeps getting decremented by the specified Step value.

Example 3 – Entering Serial Number in 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),

Here is an example that quickly enters serial numbers in all the selected cells.

Sub EnterSerialNumber() Dim Rng As Range Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 To RowCount ActiveCell.Offset(Counter - 1, 0).Value = Counter Next Counter End Sub

The above code first counts the number of selected rows and then assigns this value to the variable RowCount. We then run the loop from ‘1 to RowCount’.

Also note that since selection can be any number of rows, we have Set the variable Rng to Selection (with the line ‘Set Rng = Selection’). Now we can use the ‘Rng’ variable to refer to the selection in our code.

Example 4 – Protect All Worksheets in the Active Workbook

You can use the ‘For Next’ loop to go through all the worksheets in the active workbook, and protect (or unprotect) each of the worksheets.

Below is the code that will do this:

Sub ProtectWorksheets() Dim i As Integer For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets(i).Protect Next i End Sub

The above code counts the number of sheets by using ActiveWorkbook.Worksheets.Count. This tells VBA how many times the loop needs to be run.

In each instance, it refers to the Ith workbook (using Worksheets(i)) and protects it.

You can use this same code to Unprotect worksheets too. Just change the line Worksheets(i).Protect to Worksheets(i).UnProtect.

Nested ‘For Next’ Loops

You can use nested ‘For Next’ loops to get more complex automation done in Excel. A nested ‘For Next’ loop would mean that there is a ‘For Next’ loop within a ‘For Next’ loop.

Let me show you how to use this using an example.

Suppose I have 5 workbooks open in my system and I want to protect all the worksheets in all these workbooks.

Below is the code that will do this:

Sub ProtectWorksheets() Dim i As Integer Dim j As Integer For i = 1 To Workbooks.Count For j = 1 To Workbooks(i).Worksheets.Count Workbooks(i).Worksheets(j).Protect Next j Next i End Sub

The above is a nested For Next loop as we have used one For Next loop within another.

‘EXIT For’ Statements in For Next Loops

‘Exit For’ statement allows you to exit the ‘For Next’ loop completely.

You can use it in cases where you want the For Next loop to end when a certain condition is met.

Let’s take an example where you have a set of numbers in Column A and you want to highlight all the negative numbers in red font. In this case, we need to analyze each cell for its value and then change the font color accordingly.

But to make the code more efficient, we can first check if there are any negative values in the list or not. If there are no negative values, we can use the Exit For the statement to simply come out of the code.

Below is the code that does this:

Sub HghlightNegative() Dim Rng As Range Set Rng = Range("A1", Range("A1").End(xlDown)) Counter = Rng.Count For i = 1 To Counter If WorksheetFunction.Min(Rng) >= 0 Then Exit For If Rng(i).Value < 0 Then Rng(i).Font.Color = vbRed Next i End Sub

When you use the ‘Exit For’ statement within a nested ‘For Next’ loop, it will come out of the loop in which it is executed and go on to execute the next line in the code after the For Next loop.

For example, in the below code, the ‘Exit For’ statement will get you out of the inner loop, but the outer loop would continue to work.

Sub SampleCode() For i = 1 To 10 For j = 1 to 10 Exit For Next J Next i End Sub

Do While Loop

A ‘Do While’ loop allows you to check for a condition and run the loop while that condition is met (or is TRUE).

There are two types of syntax in the Do While Loop.

Do [While condition] [Code block to Execute] Loop

and

Do [Code block to Execute] Loop [While condition]

The difference between these two is that in the first, the While condition is checked first before any code block is executed, and in the second case, the code block is executed first and then the While condition is checked.

This means that if the While condition is False is both the cases, the code will still run at least once in the second case (as the ‘While’ condition is checked after the code has been executed once).

Now let’s see some examples of using Do While loops in VBA.

Example 1 – Add First 10 Positive Integers using VBA

Suppose you want to add the first ten positive integers using the Do While loop in VBA.

To do this, you can use the Do While loop until the next number is less than or equal to 10. As soon as the number is greater than 1o, your loop would stop.

Here is the VBA code that will run this Do While loop and the show the result in a message box.

Sub AddFirst10PositiveIntegers() Dim i As Integer i = 1 Do While i <= 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub

The above loop continues to work until the value of ‘i’ becomes 11. As soon as it becomes 11, the loop ends (as the While condition becomes False).

Within the loop, we have used a Result variable that holds the final value Once the loop is completed, a message box shows the value of the ‘Result’ variable.

Example 2 –  Enter Dates For the Current Month

Let’s say you want to enter all the dates of the current month into a worksheet column.

You can do that by using the following Do While loop code:

Sub EnterCurrentMonthDates() Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial(Year(Date), Month(Date), 1) Do While Month(CMDate) = Month(Date) Range("A1").Offset(i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub

The above code would enter all the dates in the first column of the worksheet (starting from A1). The loops continue till the Month value of the variable ‘CMDate’ matches that of the current month.

Exit Do Statement

You can use the Exit Do statement to come out of the loop. As soon as the code executes the ‘Exit Do’ line, it comes out of the Do While loop and passes the control to the next line right after the loop.

For example, if you want to enter the first 10 dates only, then you can exit the loop as soon as the first 10 dates are entered.

The below code will do this:

Sub EnterCurrentMonthDates() Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial(Year(Date), Month(Date), 1) Do While Month(CMDate) = Month(Date) Range("A1").Offset(i, 0) = CMDate i = i + 1 If i >= 10 Then Exit Do CMDate = CMDate + 1 Loop End Sub

In the above code, the IF statement is used to check if the value of i is greater than 10 or not. As soon as the value of ‘i’ becomes 10, Exit Do statement is executed and the loop ends.

Do Until Loop

‘Do Until’ loops are very much like the ‘Do While’ loops.

In ‘Do While’, the loop runs till the given condition is met, while in ‘Do Until’, it loops until the specified condition is met.

There are two types of syntax in the Do Until Loop.

Do [Until condition] [Code block to Execute] Loop

and

Do [Code block to Execute] Loop [Until condition]

The difference between these two is that in the first, the Until condition is checked first before any code block is executed, and in the second case, the code block is executed first and then the Until condition is checked.

This means that if the Until condition is TRUE is both cases, the code will still run at least once in the second case (as the ‘Until’ condition is checked after the code has been executed once).

Now let’s see some examples of using Do Until loops in VBA.

Note: All the examples for Do Until are the same as that of Do While. These have been modified to show you how the Do Until loop works.

Example 1 – Add First 10 Positive Integers using VBA

Suppose you want to add the first ten positive integers using the Do Until loop in VBA.

To do this, you need to run the loop until the next number is less than or equal to 10. As soon as the number is greater than 1o, your loop would stop.

Here is the VBA code that will run this loop and show the result in a message box.

Sub AddFirst10PositiveIntegers() Dim i As Integer i = 1 Do Until i > 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub

The above loop continues to work until the value of ‘i’ becomes 11. As soon as it becomes 11, the loop ends (as the ‘Until’ condition becomes True).

Example 2 –  Enter Dates For the Current Month

Let’s say you want to enter all the dates of the current month into a worksheet column.

You can do that by using the following Do Until loop code:

Sub EnterCurrentMonthDates() Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial(Year(Date), Month(Date), 1) Do Until Month(CMDate) <> Month(Date) Range("A1").Offset(i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub

The above code would enter all the dates in the first column of the worksheet (starting from A1). The loop continues until the Month of variable CMDate is not equal to that of the current month.

Exit Do Statement

You can use the ‘Exit Do’ statement to come out of the loop.

As soon as the code executes the ‘Exit Do’ line, it comes out of the Do Until loop and passes the control to the next line right after the loop.

For example, if you want to enter the first 10 dates only, then you can exit the loop as soon as the first 10 dates are entered.

The below code will do this:

Sub EnterCurrentMonthDates() Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial(Year(Date), Month(Date), 1) Do Until Month(CMDate) <> Month(Date) Range("A1").Offset(i, 0) = CMDate i = i + 1 If i >= 10 Then Exit Do CMDate = CMDate + 1 Loop End Sub

In the above code, as soon as the value of ‘i’ becomes 10, Exit Do statment is executed and the loop ends.

For Each

In VBA, you can loop through a set of collections using the ‘For Each’ loop.

Here are some examples of collections in Excel VBA:

  • A collection of all the open Workbooks.
  • A collection of all worksheets in a workbook.
  • A collection of all the cells in a range of selected cells.
  • A collection of all the charts or shapes in the workbook.

Using the ‘For Each’ loop, you can go through each of the objects in a collection and perform some action on it.

For example, you can go through all the worksheets in a workbook and protect these, or you can go through all the cells in the selection and change the formatting.

With the ‘For Each’ loop (also referred to as the ‘For Each-Next’ loop), you don’t need to know how many objects are there in a collection.

‘For Each’ loop would automatically go through each object and perform the specified action. For example, if you want to protect all the worksheets in a workbook, the code would be the same whether you have a workbook with 3 worksheets or 30 worksheets.

Here is the syntax of For Each-Next loop in Excel VBA.

For Each element In collection [Code Block to Execute] Next [element]

Now let’s see a couple of examples of using the For Each Loop in Excel.

Example 1 – Go through All the Worksheets in a Workbook (and Protect it)

Suppose you have a workbook where you want to protect all the worksheets.

Below For Each-Next loop can do this easily:

Sub ProtectSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Protect Next ws End Sub

In the above code, we have defined ‘ws’ variable as a Worksheet object. This tells VBA that ‘ws’ should be interpreted as a worksheet object in the code.

Now we use the ‘For Each’ statement to go through each ‘ws’ (which is a worksheet object) in the collection of all the worksheets in the active workbook (given by ActiveWorkbook.Worksheets).

Note that unlike other loops where we have tried to protect all the worksheets in a workbook, here we don’t need to worry about how many worksheets are there in the workbook.

We don’t need to count these to run the loop. For Each loop ensures that all the objects are analyzed one by one.

Example 2 – Go through All the Open Workbooks (and Save All)

If you work with multiple workbooks at the same time, it can come in handy to be able to save all these workbooks at once.

Below VBA code can do this for us:

Sub SaveAllWorkbooks() Dim wb As Workbook For Each wb In Workbooks wb.Save Next wb End Sub

Note that in this code, you don’t get a prompt that asks you to save the workbook in a specific location (if saving it for the first time).

It saves it in the default folder (it was the ‘Documents’ folder in my case). This code works best when these files are already saved and you’re making changes and you want to save all the workbooks quickly.

Example 3 – Go through All the Cells in a Selection (Highlight negative values)

Using the ‘For Each’ loop, you can loop through all the cells in a specific range or in the selected range.

This can be helpful when you want to analyze each cell and perform an action based on it.

For example, below is the code that will go through all the cells in the selection and change the cell color of the cells with negative values to red.

Sub HighlightNegativeCells() Dim Cll As Range For Each Cll In Selection If Cll.Value < 0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub

(Note I’ve used Cll as a short variable name for Cell. It’s advisable not to use object names such as Sheets or Range as variable names)

In the above code, the For Each-Next loop goes through the collection of cells in the selection. IF statement is used to identify if the cell value is negative or not. In case it is, the cell is given a red interior color, else it goes to the next cell.

In case you don’t have a selection, and instead want VBA to select all the filled cells in a column, starting from a specific cell (just like we use Control + Shift + Down arrow key to select all filled cells), you can use the below code:

Sub HighlightNegativeCells() Dim Cll As Range Dim Rng As Range Set Rng = Range("A1", Range("A1").End(xlDown)) For Each Cll In Rng If Cll.Value < 0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub

In the above example, it doesn’t matter how many filled cells are there. It will start from cell A1 and analyze all the contiguous filled cells in the column.

You also don’t need to have cell A1 selected. You can have any far-off cell selected and when the code runs, it will still consider all the cells in column A (starting from A1) and color the negative cells.

‘Exit For’ Statment

You can use the ‘Exit For’ statement in the For Each-Next loop to come out of the loop. This is usually done in case a specific condition is met.

For example, in Example 3, as we are going through a set of cells, it can be more efficient to check if there are any negative values or not. In case there are no negative values, we can simply exit the loop and save some VBA processing time.

Below is the VBA code that will do this:

Sub HighlightNegativeCells() Dim Cll As Range For Each Cll In Selection If WorksheetFunction.Min(Selection) >= 0 Then Exit For If Cll.Value < 0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub

Where to Put the VBA Code

Wondering where the VBA code goes in your Excel workbook?

Excel has a VBA backend called the VBA editor. You need to copy and paste the code in the VB Editor module code window.

Here are the steps to do this:

  1. Go to the Developer tab.IF Then Else in Excel VBA - Developer Tab in ribbon
  2. Click on the Visual Basic option. This will open the VB editor in the backend.Click on Visual Basic
  3. In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. If you don’t see the Project Explorer go to the View tab and click on Project Explorer.
  4. Go to Insert and click on Module. This will insert a module object for your workbook.VBA Loops - inserting module
  5. Copy and paste the code in the module window.VBA Loops - inserting module

You May Also Like the Following Excel Tutorials:

Sours: https://trumpexcel.com/vba-loops/
  1. A smoothie meme
  2. R pod reviews 2015
  3. Asian boy with glasses
  4. Mahogany plant stands
  5. Synonym of love

For...Next statement

Repeats a group of statements a specified number of times.

Syntax

Forcounter=startToend [ Stepstep ]
[ statements ]
[ Exit For ]
[ statements ]
Next [ counter ]


The For…Next statement syntax has these parts:

PartDescription
counterRequired. Numeric variable used as a loop counter. The variable can't be a Boolean or an array element.
startRequired. Initial value of counter.
endRequired. Final value of counter.
stepOptional. Amount counter is changed each time through the loop. If not specified, step defaults to one.
statementsOptional. One or more statements between For and Next that are executed the specified number of times.

The stepargument can be either positive or negative. The value of the step argument determines loop processing as follows.

ValueLoop executes if
Positive or 0counter <= end
Negativecounter >= end

After all statements in the loop have executed, step is added to counter. At this point, either the statements in the loop execute again (based on the same test that caused the loop to execute initially), or the loop is exited and execution continues with the statement following the Next statement.

Tip

Changing the value of counter while inside a loop can make it more difficult to read and debug your code.

Any number of Exit For statements may be placed anywhere in the loop as an alternate way to exit. Exit For is often used after evaluating some condition, for example If...Then, and transfers control to the statement immediately following Next.

You can nest For...Next loops by placing one For...Next loop within another. Give each loop a unique variable name as its counter. The following construction is correct:

Note

If you omit counter in a Next statement, execution continues as if counter is included. If a Next statement is encountered before its corresponding For statement, an error occurs.

Example

This example uses the For...Next statement to create a string that contains 10 instances of the numbers 0 through 9, each string separated from the other by a single space. The outer loop uses a loop counter variable that is decremented each time through the loop.

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Sours: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/fornext-statement

MS Excel: How to use the FOR...NEXT Statement (VBA)

Example (as VBA Function)

The FOR...NEXT statement can only be used in VBA code in Microsoft Excel.

Let's look at how to create a FOR loop in Microsoft Excel, starting with a single loop, double loop, and triple loop, and then exploring how to change the value used to increment the counter each pass through the loop.

Single Loop

The simplest implementation of the FOR loop is to use the FOR...NEXT statement to create a single loop. This will allow you to repeat VBA code a fixed number of times.

For example:

Sub Single_Loop_Example Dim LCounter As Integer For LCounter = 1 To 5 MsgBox (LCounter) Next LCounter End Sub

In this example, the FOR loop is controlled by the LCounter variable. It would loop 5 times, starting at 1 and ending at 5. Each time within the loop, it would display a message box with the value of the LCounter variable. This code would display 5 message boxes with the following values: 1, 2, 3, 4, and 5.

Single Loop - Changing Increment

By default, the FOR loop will increment its loop counter by 1, but this can be customized. You can use to change the value used to increment the counter. The FOR loop can be increment can be either positive or negative values.

Positive Increment

Let's first look at an example of how to increment the counter of a FOR loop by a positive value.

For example:

Sub Increment_Positive_Example Dim LCounter As Integer For LCounter = 1 To 9 Step 2 MsgBox LCounter Next LCounter End Sub

In this example, we've used in the FOR loop to change the increment to 2. What this means is that the FOR loop would start at 1, increment by 2, and end at 9. The code would display 5 message boxes with the following values: 1, 3, 5, 7, and 9.

Negative Increment

Now, let's look at how to increment the counter of a FOR loop by a negative value.

For example:

Sub Increment_Negative_Example Dim LCounter As Integer For LCounter = 50 To 30 Step -5 MsgBox LCounter Next LCounter End Sub

When you increment by a negative value, you need the starting number to be the higher value and the ending number to be the lower value, since the FOR loop will be counting down. So in this example, the FOR loop will start at 50, increment by -5, and end at 30. The code would display 5 message boxes with the following values: 50, 45, 40, 35, and 30.

Double Loop

Next, let's look at an example of how to create a double FOR loop in Microsoft Excel.

For example:

Sub Double_Loop_Example Dim LCounter1 As Integer Dim LCounter2 As Integer For LCounter1 = 1 To 4 For LCounter2 = 8 To 9 MsgBox LCounter1 & "-" & LCounter2 Next LCounter2 Next LCounter1 End Sub

Here we have 2 FOR loops. The outer FOR loop is controlled by the LCounter1 variable. The inner FOR loop is controlled by the LCounter2 variable.

In this example, the outer FOR loop would loop 4 times (starting at 1 and ending at 4) and the inner FOR loop would loop 2 times (starting at 8 and ending at 9). Within the inner loop, the code would display a message box each time with the value of the LCounter1-LCounter2. So in this example, 8 message boxes would be displayed with the following values: 1-8, 1-9, 2-8, 2-9, 3-8, 3-9, 4-8, and 4-9.

Triple Loop

Next, let's look at an example of how to create a triple FOR loop in Microsoft Excel.

For example:

Sub Triple_Loop_Example Dim LCounter1 As Integer Dim LCounter2 As Integer Dim LCounter3 As Integer For LCounter1 = 1 To 2 For LCounter2 = 5 To 6 For LCounter3 = 7 To 8 MsgBox LCounter1 & "-" & LCounter2 & "-" & LCounter3 Next LCounter3 Next LCounter2 Next LCounter1 End Sub

Here we have 3 FOR loops. The outer-most FOR loop is controlled by the LCounter1 variable. The next FOR loop is controlled by the LCounter2 variable. The inner-most FOR loop is controlled by the LCounter3 variable.

In this example, the outer-most FOR loop would loop 2 times (starting at 1 and ending at 2) , the next FOR loop would loop 2 times (starting at 5 and ending at 6), and the inner-most FOR loop would loop 2 times (starting at 7 and ending at 8).

Within the inner-most loop, the code would display a message box each time with the value of the LCounter1-LCounter2-LCounter3. This code would display 8 message boxes with the following values: 1-5-7, 1-5-8, 1-6-7, 1-6-8, 2-5-7, 2-5-8, 2-6-7, and 2-6-8.

Example#1 from Video

In the first video example, we are going to use the For...Next statement to loop through the products in column A and update the appropriate application type in column B.

Sub totn_for_loop_example1() Dim LCounter As Integer For LCounter = 2 To 4 If Cells(LCounter, 1).Value = "Excel" Then Cells(LCounter, 2).Value = "Spreadsheet" ElseIf Cells(LCounter, 1).Value = "Access" Then Cells(LCounter, 2).Value = "Database" ElseIf Cells(LCounter, 1).Value = "Word" Then Cells(LCounter, 2).Value = "Word Processor" End If Next LCounter End Sub

Example#2 from Video

In the second video example, we have a list of participants in column A and we'll use two FOR Loops to assign each of the participants to either Team A or Team B (alternating between the two).

Sub totn_for_loop_example2() Dim LCounter1 As Integer Dim LCounter2 As Integer For LCounter1 = 2 To 9 Step 2 Cells(LCounter1, 2).Value = "Team A" Next LCounter1 For LCounter2 = 3 To 9 Step 2 Cells(LCounter2, 2).Value = "Team B" Next LCounter2 End Sub
Sours: https://www.techonthenet.com/excel/formulas/for_next.php

Step for vba to

Step Keyword

You can use the Step keyword in Excel VBA to specify a different increment for the counter variable of a loop.

1. Place a command button on your worksheet and add the following code lines:

Dim i AsInteger

For i = 1 To 6 Step 2
    Cells(i, 1).Value = 100
Next i

Result when you click the command button on the sheet:

Positive Step

Explanation: The code lines between For and Next will be executed three times. For i = 1, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When Excel VBA reaches Next i, it increases i with 2 and jumps back to the For statement. For i = 3, Excel VBA enters the value 100 into the cell at the intersection of row 3 and column 1, etc.

2. Place a command button on your worksheet and add the following code lines:

Dim j AsInteger

For j = 8 To 3 Step -1
    Cells(6, j).Value = 50
Next j

Result when you click the command button on the sheet:

Negative Step

Explanation: The code lines between For and Next will be executed six times. For j = 8, Excel VBA enters the value 50 into the cell at the intersection of row 6 and column 8. When Excel VBA reaches Next j, it decreases j with 1 and jumps back to the For statement. For j = 7, Excel VBA enters the value 50 into the cell at the intersection of row 6 and column 7, etc.

Sours: https://www.excel-easy.com/vba/examples/step-keyword.html
Using For Next Loop - Excel VBA

What does the To and Step mean in VBA?

In above code loop will iterate from 1 to 14 increment with 3 so output will be like

It means it can not cross 14 that is limit.

So whatever value is provided in step it will add into the variable use for looping purpose. Here

But in For loop in VBA, Step value can not be changed dynamically. For example:

Here, before starting iteration Step is equal to the value of i that is the default value i.e. 0. So i will increment like below:

So i will not increment here and loop will iterate for ever.

Now for below code:

i will increment like :

so it will increment by 1 and output will be 1 2 3 .... 14

Now for below code :

here, i is equal to 3 before loop execution, so Step value will be 3, but loop will start with i = 1 and will increment with 3 through out the loop. here,

so output will be 1 4 7 10.

Now for some other variable:

in above code Step value will be 2, so i will increment by 2 for every iteration whether j is modifying inside loop or not, it will not impact Step value, so output will be

Please correct me if I miss anything or something is wrong in this. Also suggest if there is any way for dynamic looping using For loop in VBA.

answered Jun 1 '18 at 16:15

Sours: https://stackoverflow.com/questions/19687018/what-does-the-to-and-step-mean-in-vba

You will also be interested:

7 Examples of For Loops in Microsoft Excel VBA

Fb-Button

If you are writing a VBA program & you want to execute the same task multiple times then you can do this by using VBA for loops. VBA is a sequential programming language. Each line of code gets executed from top to bottom until there are no more lines of code to read. If you want to go back & perform a task, then you have to force by using macro code. You get results with loop.

The For Loop in VBA is one of the most common types of loop. The For loop has two forms: For Next and For Each In Next. The For loop is typically used to move sequentially through a list of items or numbers. To end the For loop at any given point, we can use the exit statement. For Loop will go round and round until it meets the end condition. Once the end condition is met, the programming flow will continue downward, in its natural direction.

 

The For … Next loop has the following syntax: For counter = start_counter To end_counter 'Do something here (your code) Next counter

 

We are actually creating a loop that uses variable counter as the ‘time keeper’ of the loop. We set it to a value equal to start_counter at the beginning of the loop and then increment it by 1 during each loop till it meets the end condition. The loop will execute till the time the value of the counter becomes equal to end_counter. The loop executes for the last time when both the above values match and then the loop stops.

All of the above might be confusing for some people, so let’s gear up & start learning For Loop in excel with few examples.

  • Create a new excel workbook then save it with the extension .xlsm
  • To launch Visual Basic editor screen, use ALT + F11
  • Insert New Module
  • Copy the below code in the VB standard module

 

img1

 

Loop 1 (Displaying number with msgbox)

Sub Loop1() Dim StartNumber As Integer Dim EndNumber As Integer EndNumber = 5 For StartNumber = 1 To EndNumber MsgBox StartNumber & " is " & "Your StartNumber" Next StartNumber End Sub

img2

 

Code Explanation:

  • The VBA code needs you to assign value to StartNumber, where EndNumber are variables that are declared as integers as the starting point for your loop
  • These values can be any number & we have EndNumber as 5
  • StartNumber is started at 1
  • For StartNumber = 1 To EndNumber means code will start from 1 (StartNumber) to 5 (EndNumber)
  • MsgBox StartNumber & " is " & "Your StartNumber" will display the following message box

 

img3

 

Loop2 (Fill Values)

Sub Loop2() 'Fills cells A1:A56 with values of X by looping' --- Comment 'Increase value of X by 1 in each loop' --- Comment Dim X As Integer For X = 1 To 56 Range("A" & X).Value = X Next X End Sub

 

img4

 

Code Explanation:

  • We have assigned X as an integer
  • For X = 1 to 56; this will start with 1 and continue till 56 with an increment of 1 each time
  • Range("A" & X).Value = X; this line will store the value of X and will pass to range A1 to A56

 

img5

 

Loop3 (Fill cells with background color)

Sub Loop3() ' Fills cells B1:B56 with the 56 background colours' --- Comment Dim X As Integer For X = 1 To 56 Range("B" & X).Select With Selection.Interior .ColorIndex = X .Pattern = xlSolid End With Next X End Sub

 

img6

 

Code Explanation:

  • We have assigned X as an integer
  • For X = 1 to 56 will start with 1 and continue till 56 with an increment of 1 each time
  • Range("B" & X).Select; this line will store the value of X & select the cell B1 till B56
  • The next 4 lines i.e. With Selection.Interior will select interior colorindex & take the value from X of that colorindex such that 1 belongs to color black; 2 belongs to color white; 3 for red & so on

 

img7

 

Loop 4 (Fill Values with increment of 2)

By default, the Step value is forward 1, however it can be set to a number more than 1.

Sub Loop4() ' Fills every second cell from C1:C50 with values of X' --- Comment Dim X As Integer For X = 1 To 50 Step 2 Range("C" & X).Value = X Next X End Sub

 

img8

 

Code Explanation:

  • We have assigned X as an integer
  • For X = 1 to 50 Step 2; this will start with 1 in X till 50 with an increment of 2 each time
  • Range("C" & X).Value = X; this line will store the value of X and will pass to range C1 to C50

 

img9

 

Loop 5 (VBA For Loop in Reverse with STEP Instruction)

It is not necessary that counter in the For loop will only move from low to higher values; instead, For loop can run backwards, too i.e. high to lower values.

Even though the Step value is forward 1 by default, however, it can be set to a number in reverse order.

Sub Loop5() ' Fills cells from D1:D50 with values of X' --- Comment ' In this case X decreases by 1' --- Comment Dim X As Integer, Row As Integer Row = 1 For X = 50 To 0 Step -1 Range("D" & Row).Value = X Row = Row + 1 Next X End Sub

 

img10

 

Code Explanation:

  • We have assigned X & Row as integer
  • Row contains value 1
  • For X = 50 to 0 Step -1; this will start from 50 with decrement by 1 in X till 0
  • Range("D" &Row).Value = X; this line will store the value of X and will pass to range D1 to D50

 

img11

 

Loop 6 (Fills every second cell in Reverse with STEP-2)

In the above For loop example, we can use the Step and order to see if the For loop works in forward or backward direction.

Sub Loop6() ' Fills every second cell from E1:E100 with values of X' --- Comment ' In this case X decreases by 2' --- Comment Dim X As Integer, Row As Integer Row = 1 For X = 100 To 0 Step -2 Range("E" & Row).Value = X Row = Row + 2 Next X End Sub

 

img12

 

Code Explanation:

  • We have assigned X & Row as integer
  • Row contains value 1
  • For X = 100 to 0 Step -2; this will start from 100 with decrement by 2 in X till 0
  • Range("E" &Row).Value = X; this line will store the value of X and will pass to range E1 to E100

 

img13

 

Loop 7 (For Loop with IF condition: Fills cells starting from specific cell)

This will fill the cells from cell F11 with value 11 till X meets the IF condition

Sub Loop7() ' Starts to fill cells F11:F100 with values of X' --- Comment ' This will exit from the loop after 50' --- Comment Dim X As Integer For X = 11 To 100 Range("F" & X).Value = X If X = 50 Then MsgBox ("Bye Bye") Exit For End If Next X End Sub

 

img14

 

Code Explanation:

  • We have assigned X as an integer
  • For X = 11 to 100; will start from 11 with increment by 1 in X till the condition meets
  • Range("F" &X).Value = X; this line will store the value of X and will pass to range F11 till the condition meets

 

img15

 

  • After entering the value 50 in cell F50, the following message box will be displayed

 

img16

 

Conclusion: With the above 7 examples, we can apply For loop in our regular or any automation part.

 

image 48
 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]

 

 

Sours: https://www.exceltip.com/using-loops/for-loops-with-7-examples.html


5161 5162 5163 5164 5165