VBA For Loop – A Complete Guide

by Paul Kelly | | VBA Language | 40 comments

This post provides a complete guide to the standard VBA For Loop and the VBA For Each Loop.

If you are looking for information about the VBA While and VBA Do Loop then go here.

If you want some quick info about the For loops then check out the Quick Guide table in the section below.

If you are looking for information on a particular topic then check out the Table of Contents below.

“History is about loops and continuums” – Mike Bidlo.

Contents

A Quick Guide to the VBA For Loop

Loop format	Description	Example
For ... Next	Run 10 times	For i = 1 To 10 Next
For ... Next	Run 5 times. i=2,4, 6 etc.	For i = 2 To 10 Step 2 Next
For ... Next	Run in reverse order	For i = 10 To 1 Step -1 Debug.Print i Next
For ... Next	Go through Collection	For i = 1 To coll.Count Debug.Print coll(i) Next
For ... Next	Go through array	For i = LBound(arr) To UBound(arr) Debug.Print arr(i) Next i
For ... Next	Go through 2D array	For i = LBound(arr) To UBound(arr) For j = LBound(arr,2) To UBound(arr,2) Debug.Print arr(i, j) Next j Next i
For Each ... Next	Go through Collection	Dim item As Variant For Each item In coll Debug.Print item Next item
For Each ... Next	Go through array	Dim item As Variant For Each item In arr Debug.Print item Next item
For Each ... Next	Go through 2D array	Dim item As Variant For Each item In arr Debug.Print item Next item
For Each ... Next	Go through Dictionary	Dim key As Variant For Each key In dict.Keys Debug.Print key, dict(key) Next key
Both types	Exit Loop	For i = 1 To 10 If Cells(i,1) = "found" Then Exit For End If Next i

The VBA For Loop Webinar

If you are a member of the website, click on the image below to view the webinar for this post.

(Note: Website members have access to the full webinar archive.)

Introduction to the VBA For Loop

Loops are by far the most powerful component of VBA. They are the rocket fuel of your Macros. They can perform tasks in milliseconds that would take humans hours. They also dramatically reduce the lines of code your applications need.

For Loops have been part of all major programming languages since they were first used with Fortan in 1957.

If you have never used loops before then this post is a great place to start. It provides an in-depth guide to loops, written in plain English without the jargon.

Let’s start with a very important question – what are loops and why do we need them?

What are VBA For Loops?

A loop is simply a way of running the same lines of code a number of times. Obviously running the same code over and over would give the same result.

So what is important to understand is that the lines of code normally contain a variable that changes slightly each time the loop runs.

For example, a loop could write to cell A1, then cell A2, A3 and so on. The slight change each time is the row.

Let’s look at a simple example.

VBA For Loop Example 1

The following code prints the values 1 to 5 in the Immediate Window(Ctrl + G to view).

Debug.Print 1
Debug.Print 2
Debug.Print 3
Debug.Print 4
Debug.Print 5

The Immediate Window

If you have not used the Immediate Window before then this section will get you up to speed quickly.

The function Debug.Print writes values to the Immediate Window. To view this window select View->Immediate Window from the menu( the shortcut is Ctrl + G)

VBA For Loop Example 2

Now imagine we want to print out the numbers 1 to 20. We would need to add 15 more lines to the example above.

However, using a loop we only need to write Debug.Print once.

    For i = 1 To 20
        Debug.Print i
    Next i

The output is:

Output

If we needed print the numbers 1 to 1000 then we only need to change the 20 to 1000.

Normally when we write code we would use a variable instead of a number like 20 or 1000. This gives you greater flexibility. It allows you to decide the number of times you wish to run the loop when the code is running. The following example explains this.

VBA For Loop Example 3

A common task in Excel is read all the rows with with data.

The way you approach this task is as follows

Find the last row with data
Store the value in variable
Use the variable to determine how many times the loop runs

Using a variable in the loop makes your code very flexible. Your will work no matter how many rows there are.

Let’s have a look at an example. Imagine you receive a sheet with a list of fruit types and their daily sales. You want to count the number of Oranges sold and this list will vary in size depending on sales.

The following screenshot shows an example of this list

Sample Data of Fruit Sales

We can use the code to count the oranges

' https://excelmacromastery.com/
Sub CountFruit()

    ' Get the last row with text
    Dim LastRow As Long
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row

    Dim i As Long, Total As Long
    ' Use LastRow in loop
    For i = 2 To LastRow
        ' Check if cell has text "Orange"
        If Sheet1.Cells(i, 1).Value = "Oranges" Then
            ' Add value in column B to total
            Total = Total + Sheet1.Cells(i, 2).Value
        End If
    Next i

    ' Print total
    Debug.Print "Total oranges sold was:"; Total

End Sub

You can try this code for yourself. Change the number of fruit items and you will see that the code still works fine.

If you were to increase the number fruit items to a large value like 10,000 then you will hardly notice the difference in the time it takes to run – almost instantly.

Loops are super fast. This is what makes them so powerful. Imagine performing a manual task on 10,000 cells. It would take a considerable amount of time.

Advantages of the VBA For Loop

4To conclude this section we will list the major advantages of using loops

They reduce the lines code you need
They are flexible
They are fast

In the next sections we will look at the different types of loops and how to use them.

The Standard VBA For Loop

The VBA For loop is the most common loop you will use in Excel VBA. The For Loop is used when you can determine the number of times it will be run. For example, if you want to repeat something twenty times.

YouTube Video For Loop

Check out this YouTube Video of the For Loop:

Get the workbook and code for this video here

Format of the Standard VBA For Loop

The Standard VBA For Loop has the following format:

For <variable> = <start value> to <end value>
Next <variable>

The start and end values can be variables. Also the variable after Next is optional but it is useful and it makes it clear which for loop it belongs to.

How a For Loop Works

Let’s look at a simple for loop that prints the numbers 1 to 3

    Dim i As Long
    For i = 1 To 3
        Debug.Print i
    Next i

How this code works is as follows

i is set to 1
The value of i(now 1) is printed

i is set to 2
The value of i(now 2) is printed

i is set to 3
The value of i(now 3) is printed

If we did not use a loop then the equivalent code would be

    Dim i As Long
    i = i + 1
    Debug.Print i
    i = i + 1
    Debug.Print i
    i = i + 1
    Debug.Print i

The i = i + 1 line is used to add 1 to i and is a common way in programming to update a counter.

Using Step with the VBA For Loop

You can see that i is increased by one each time. This is the default. You can specify this interval using Step keyword.

The next example shows you how to do this:

    ' Prints the even numbers i.e. 2,4,6,8 ... 20
    For i = 2 To 20 Step 2
        Debug.Print i
    Next i

You can use a negative number with Step which will count in reverse

    ' Prints the even numbers in reverse i.e. 20,18,16,14 ... 2
    For i = 20 To 2 Step -2
        Debug.Print i
    Next i

Note: if Step is positive then your starting number must be lower than you ending number. The following loop will not run because the starting number 20 is greater than 10. VBA therefore, thinks it has already reached the target value 10.

    ' Will not run as starting number already greater than 10
    For i = 20 To 10 Step 1
        Debug.Print i
    Next i

If Step is negative then the start number must be greater than the end number.

Exit the For Loop

Sometimes you may want to leave the loop earlier if a certain condition occurs. For example if you read bad data.

You can use Exit For to automatically leave the loop as shown in the following code

    For i = 1 To 1000

        ' If cell is blank then exit for
        If Cells(i, 1) = "" Then
            MsgBox "Blank Cell found - Data error"
            Exit For
        End If

    Next i

Using the VBA For Loop with a Collection

The For loop can also be used to read items in a Collection.

In the following example, we display the name of all the open workbooks

    Dim i As Long
    For i = 1 To Workbooks.Count
        Debug.Print Workbooks(i).FullName
    Next i

Using Nested For Loops

Sometimes you may want to use a loop within a loop. An example of this would be where you want to print the names of the worksheets of each open workbook.

The first loop would go through each workbook. Each time this loop runs it would use a second loop to go through all the worksheets of that workbook. It is actually much easier to do than it sounds.

The following code shows how:

' https://excelmacromastery.com/
Sub ListWorksheets()

    Dim i As Long, j As Long
    ' First Loop goes through all workbooks
    For i = 1 To Workbooks.Count

        ' Second loop goes through all the worksheets of workbook(i)
        For j = 1 To Workbooks(i).Worksheets.Count
            Debug.Print Workbooks(i).Name + ":" + Worksheets(j).Name
        Next j

    Next i

End Sub

This works as follows:

The first loop sets i to 1

The second loop then uses the workbook at 1 to go through the worksheets.

The first loop sets i to 2

The second loop then uses the workbook at 2 to go through the worksheets.

and so on

It the next section we will use a For Each loop to perform the same task. You will find the For Each version much easier to read.

The VBA For Each Loop

The VBA For Each loop is used to read items from a collection or an array. We can use the For Each loop to access all the open workbooks. This is because Application.Workbooks is a collection of open workbooks.

This is a simple example of using the For Each Loop

    Dim wk As Workbook
    For Each wk In Workbooks
        Debug.Print wk.FullName
    Next wk

Format of the VBA For Each Loop

You can see the format of the VBA for each loop here(See Microsoft For Each Next documentation):
For Each <variable> in <collection>
Next <variable>

To create a For Each loop we need a variable of the same type that the collection holds. In the example here we created a variable of type Workbook.

If the collection has different types of items we can declare the variable as a variant.

VBA contains a collection called Sheets. This is a collection of sheets of type Worksheet(normal) and Chart(when you move a chart to be a full sheet). To go through this collection you would declare the variable as a Variant.

The following code uses For Each to print out the name of all the sheets in the current workbook

    Dim sh As Variant
    For Each sh In ThisWorkbook.Sheets
        Debug.Print sh.Name
    Next sh

Order of Items in the For Loop

For Each goes through items in one way only.

For example, if you go through all the worksheets in a workbook it will always go through from left to right. If you go through a range it will start at the lowest cell e.g. Range(“A1:A10”) will return A1,A2,A3 etc.

This means if you want any other order then you need to use the For loop.

Both loops in the following example will read the worksheets from left to right:

    ' Both loops read the worksheets from left to right
    Dim wk As Worksheet
    For Each wk In ThisWorkbook.Worksheets
        Debug.Print wk.Name
    Next

    Dim i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        Debug.Print ThisWorkbook.Worksheets(i).Name
    Next

As you can see the For Each loop is neater to write. However if you want to read the sheets in any other order e.g. right to left then you have to use the for loop:

    ' Reading the worksheets from right to left
    Dim i As Long
    For i = ThisWorkbook.Worksheets.Count To 1 Step -1
        Debug.Print ThisWorkbook.Worksheets(i).Name
    Next

Using the VBA For Each Loop With Arrays

One thing to keep in my is that the For Each loop is that it is read-only when you use it with arrays.

The following example demonstrates this:

' https://excelmacromastery.com/
Sub UseForEach()

    ' Create array and add three values
    Dim arr() As Variant
    arr = Array("A", "B", "C")

    Dim s As Variant
    For Each s In arr
        ' Changes what s is referring to - not value of array item
        s = "Z"
    Next

    ' Print items to show the array has remained unchanged
    For Each s In arr
        Debug.Print s
    Next

End Sub

In the first loop we try to assign s to “Z”. When happens is that s is now referring the string “Z” and no longer to the item in the array.

In the second loop we print out the array and you can see that none of the values have changed.

When we use the For Loop we can change the array item. If we change the previous code to use the For Loop you it will change all the array values to “Z”

' https://excelmacromastery.com/
Sub UsingForWithArray()

    ' Create array and add three values
    Dim arr() As Variant
    arr = Array("A", "B", "C")

    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        ' Changes value at position to Z
        arr(i) = "Z"
    Next

    ' Print items to show the array values have change
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next

End Sub

If your Collection is storing Objects the you can change the items using a For Each loop.

Using Nested For Each Loops

We saw already that you can have a loop inside other loops. Here is the example from above:

' https://excelmacromastery.com/
Sub ListWorksheets()

    Dim i As Long, j As Long
    ' First Loop goes through all workbooks
    For i = 1 To Workbooks.Count

        ' Second loop goes through all the worksheets of workbook(i)
        For j = 1 To Workbooks(i).Worksheets.Count
            Debug.Print Workbooks(i).Name + ":" + Worksheets(j).Name
        Next j

    Next i

End Sub

This time we will use the For Each loop to perform the same task:

' https://excelmacromastery.com/
Sub ReadAllWorksheets()

    Dim wk As Workbook, sh As Worksheet
    ' Read each workbook
    For Each wk In Workbooks

        ' Read each worksheet in the wk workbook
        For Each sh In wk.Worksheets
            ' Print workbook name and worksheet name
            Debug.Print wk.Name + ": " + sh.Name
        Next sh

    Next wk

End Sub

As you can see this is a neater way of performing this task than using the For Loop:

This code run as follows:

Get the first Workbook from the Workbooks collection
Go through all the worksheets in this workbook
Print the workbook/worksheet details
Get the next workbooks in the collection
Repeat steps 2 to 3
Continue until no more workbooks are left in the collection

How to Loop Through a Range

In Excel VBA, the most common use of a For Loop is to read through a range.

Imagine we have the data set in the screenshot below. Our task is to write code that will read through the data and copy the amounts to the column J. We are only going to copy amounts that are greater than 200,000.

The following example shows how we do it:

' Read through an Excel Range using the VBA For Loop
' https://excelmacromastery.com/
Sub ForLoopThroughRange()

    ' Get the worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    
    ' Get the Range
    Dim rg As Range
    Set rg = sh.Range("A1").CurrentRegion
    
    ' Delete existing output
    sh.Range("J1").CurrentRegion.ClearContents
    
    ' Set the first output row
    Dim row As Long
    row = 1
    
    ' Read through all the rows using the For Loop
    Dim i As Long
    For i = 2 To rg.Rows.Count
    
        ' Check if amount is greater than 200000
        If rg.Cells(i, 4).Value > 200000 Then
        
            ' Copy amount to column m
            sh.Cells(row, "J").Value = rg.Cells(i, 4).Value
            
            ' Move to next output row
            row = row + 1
            
        End If
        
    Next i
    
End Sub

This is a very basic example of copying data using Excel VBA. If you want a complete guide to copying data using Excel VBA then check out this post

Summary of the VBA For Loops

The Standard VBA For Loop

The For loop is slower than the For Each loop.
The For loop can go through a selection of items e.g. 5 to 10.
The For loop can read items in reverse e.g. 10 to 1.
The For loop is not as neat to write as the For Each Loop especially with nested loops.
To exit a For loop use Exit For.

The VBA For Each Loop

The For Each loop is faster than the For loop.
The For Each loop goes through all items in the collection\array.
The For Each loop can go through items in one order only.
The For Each loop is neater to write than a For Loop especially for nested loops.
To exit a For Each loop use Exit For.

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

40 Comments

K-Li-Ch on April 3, 2015 at 1:30 am

Paul:

In Sub ListWorksheets() you must change Worksheets(i) to Worksheets(j). Also I was getting “error 9” at execution. I changed

Debug.Print Workbooks(i).Name + “:” + Worksheets(j).Name

to

Debug.Print Workbooks(i).Name + “:” + Workbooks(i).Worksheets(j).Name

and it worked perfectly.

Sincerely yours,

K-Li-Ch
Reply
- Paul Kelly on April 3, 2015 at 6:25 pm
  
  Thanks K-Li-Ch,
  
  I’ve updated the code to correct that error.
  
  Paul
  Reply
Paul Kelly on April 16, 2016 at 11:55 am

Yes it would be a better way to write it.
Reply
K-Li-Ch on August 4, 2016 at 4:43 am

Or “While” a condition is met. Same with Do … Loop While.

Sincerely,

K-Li-Ch
Reply
farahxx on February 16, 2017 at 6:29 am

Private Sub CommandButton3_Click()

‘ Get the last row with text
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Dim i As Long, Total As Long

‘ Use LastRow in loop
For i = 1 To LastRow

‘ Check if cell has text “Oranges1”
If Cells(i, 1).Value = “Oranges1” Then

‘ Add value in column B to total
Total = Sheets(1).Cells(i, 2).Value + Sheets(2).Cells(i, 2).Value

Exit For
End If
Next i

‘ Print total
MsgBox “Total oranges sold was = ” & Total

End Sub

////////////////////////

Hi Paul!
I’m having a situation where I want to use the total gain from this looping to another excel workbook and at the same time I don’t want to use the msgbox but just want the result and put it on another workbook. Is there’s any possibilities that I can do that? What should I add to the code? I have tried some of this;

Set Target_Workbook = Workbooks.Open(Target_Path)
Set Source_Workbook = ThisWorkbook

and using this

Source_Workbook.Sheets(1).Cells(19, 4) = Target_Data

but it seems not to work out well. Can you help me with this?
Reply
- Paul Kelly on February 16, 2017 at 9:09 am
  
  You are assigning the value to the source workbook. Should this not be to the Target_Workbook? e.g.
  
  Target_Workbook.Sheets(1).Cells(19, 4) = Target_Data
  Reply
Nicolas on April 3, 2017 at 3:50 pm

Hi Paul,
Is there a way to create n for loops without typing manually every loop?
Thanks
Reply
- Paul Kelly on April 4, 2017 at 3:03 am
  
  Hi Nicolas.
  
  No. There is no mechanism for this in VBA.
  Reply
Ashish Pawar on July 25, 2017 at 12:43 pm

I have 2 worksheets with one single column common in both which is the 1st column of both worksheets. I want to match the columns in both sheets and for matching columns ( i.e. for Sheet1.Ax = Sheet2.Ax) I want all rows in that row from Sheet 2 to the last row column of the sheet 1 . I am using below code –
Dim LastCol As Long
Dim rng As Range
Dim rng2 As Range
Dim i As Integer

‘ Use all cells on the sheet
Set rng = Sheets(“Sheet1”).Cells
Set rng2 = Sheets(“Sheet2”).Cells

‘ Find the last column
LastCol = Last(2, rng)

‘ After the last column with data change the value of the cell in row 1

For i = 1 To 895
rng.Parent.Cells(i, LastCol + 1).Value = rng2.Parent.Cells(i,1j).Value
Next I

With above coe I am able to only copy the first column from Sheet2 where as I need to copy all columns for sheet2 is the 1st column in sheet2 is same as 1st column in Sheet1
Can you please help with the actual code
Reply
Frik on September 27, 2017 at 4:41 am

Good day

How do i get the sheet index number to loop for example
=’1′!$A$1
=’2′!$A$1
=’3′!$A$1
=’4′!$A$1
Reply
- Paul Kelly on September 27, 2017 at 12:53 pm
  
  Worksheets(1).Range(“$A$1”).Value
  Worksheets(2).Range(“$A$1”).Value
  Reply
Ira Marvin on October 11, 2017 at 6:53 pm

Great Content.
Reply
Sadiq Kachwala on February 2, 2018 at 11:50 am

Hello Paul, I want to code a loop that will count each column from a table (eg 16 columns table). Then it should select row with maximum count and activate first cell from the row. Can you help?
Please mail me the response.
Reply
John on March 20, 2018 at 6:12 pm

Dear Paul, good afternoon.

I’m just starting about loops and your site is plentiful of examples, Congratulations.

Please forgive my dumbness…
In the “Using Step” example above, what if we have to print from 2 to 20 and, next, from 20 to 2 in the same loop (I mean, to print this: 2, 4, 6, … 20, 20, …, 6, 4, 2)?
From my poor knowledge, it will only be possible using 2 concatenated loops. From your experience, it would be possible with a single one?
Thanks
John
Reply
- Paul Kelly on March 21, 2018 at 5:51 am
  
  Hi John,
  
  You could write code in the loop to make it happen but would be very messy.
  
  It is much better practice to use two loops for something like this. It makes the code clearer
  
  Paul.
  Reply
Mireia on March 21, 2018 at 10:30 am

Dear Paul,

I know something about VBA but never used loops. This was great help thanks!!

My question is the following: Is it possible to make a loop to run several macros? Maybe it will be more understandable with an example:

*******************************************************
Sub example()

Application.CutCopyMode = False
Sheets(“Sheet2”).Select
Application.GoTo Range(“A1”), True

Application.Run “example.xlsm!example1”
Application.CutCopyMode = False
Application.Run “example.xlsm!example2”
Application.CutCopyMode = False
Application.Run “example.xlsm!example3”
Application.CutCopyMode = False

Sheets(“Sheet2”).Select
Range(“A1”).Select

End Sub
***********************************************************************
Could I make this macro have a loop? It is a long dataset, it goes through it until a change in a cell and then does a lot of stuff. But then I want it to start again. Could it be possible?

Thank you very much in advance for all the information!!

Regards,

Mireia.
Reply
- Paul Kelly on March 22, 2018 at 4:15 am
  Hi Mireia,
  
  Sure. You can do this
  
  Dim i As Long For i = 1 To 3 Application.Run "example.xlsm!example" & i Application.CutCopyMode = False Next i
  
  Paul
  Reply
Austin Gapinski on June 8, 2018 at 2:15 am

Hi Paul,
Thank you for all the great information above!
I have a problem that I am stuggling to figure out. I am looking to update a scatter plot every x amount of minutes to keep up with some data that is being exported into the file from another program.

Would a loop be the proper way to do this? Or is there some other function out there I should be looking in to!
Reply
- Paul Kelly on June 8, 2018 at 10:42 am
  
  Hi Austin,
  
  You can use a loop with the Application.Wait function. This function will pause to code for a specific amount of time.
  Reply
Pankaj Negi on September 11, 2018 at 11:36 am

Sir,

Kindly check my code and revert me its not working properly…

Sub Main()

Dim x As Integer

Sheets(“Entry Sheet”).Select
lastrows = Range(“A2”, Range(“A2”).End(xlDown)).Rows.Count

For x = 2 To lastrows + 1
Cells(x, 1).Copy
Worksheets(“Brokerage”).Range(“D1”).PasteSpecial
Sheets(“Brokerage”).PrintOut
Next
End Sub
Reply
Benedito Almeida on December 12, 2018 at 7:39 pm

I have the row range, listed below, which repeats several in the column. What is the VBA macro code through loop, ideally to exclude this range of lines?

TOTAL ACUMULADO DAS VERBAS NA SUB-LOTAﾇAO
———————————————————————————————————————————-

———————————————————————————————————————————-

———————————————————————————————————————————-
Base INSS: 5.200,00 Sal.Familia INSS: 0,00 Base p/ Prev.Propria(*): 0,00 Sal.Fam.Prev.Propria: 0,00
Reply
- Paul Kelly on December 20, 2018 at 12:36 pm
  
  Hi Benedito,
  
  You can use an If Statement to check the line.
  
  For example
  
  If Instr(sheet1.Range(“A” & i).Values2,”TOTAL ACUMULADO DAS VERBAS”) > 0 Then
  
  Paul
  Reply
Samim Malikzay on December 13, 2018 at 8:05 am

Hi sir,

I have a question about a for loop in VBA. Could you please help me?
Question:

This (see below) code is part of a code that draws in autocad. According to the code the program searchs all cells to see if there is a command or value in de cell. I want to know if there is a way to stop the loop when there is a specific text in de cell.
I want to tell VBA to stop the Loop when this (\\C:myfolder) text is written in a cell. I have been looking for a while, if you could take a look at it and help me it would be wonderful. Thanks in advance!
code:
‘Loop through all the rows of the sheet that contain commands.
For i = 13 To LastRow

‘Find the last column.
LastColumn = .Cells(i, .Columns.Count).End(xlToLeft).Column

‘Check if there is at least on command in each row.

If LastColumn > 2 Then

‘Create a string that incorporates all the commands that exist in each row.
acadCmd = “”
For j = 3 To LastColumn
If Not IsEmpty(.Cells(i, j).Value) Then
acadCmd = acadCmd & .Cells(i, j).Value & vbCr
End If
Next j

Kind regards,

Samim
Reply
- Paul Kelly on December 20, 2018 at 12:38 pm
  
  You can use “Exit For” to exist the loop
  
  If Instr(.Cells(i, j).Value,”\\C:myfolder”) > 0 Then
  Exit For
  End If
  
  Paul
  Reply
Poornachander B on March 19, 2019 at 8:00 am

Dear Paul,

I am new to Macro’s
I have a scenario which I have two tables like below
Date Product Code Selling Price Sales per month Balance in Stock Profit Amount Loss Amount Profit in % Loss In % Total Amount
Jan 112 255 158 40290
Jan 116 152 99 15048
Feb 118 130 98 12740
Mar 118 131 135 17685
Jan 119 160 100 16000
Feb 112 300 49 14700
Feb 119 150 99 14850
Mar 116 153 143 21879
Mar 112 245 200 49000
Feb 120 150 38 5700
Feb 116 150 200 30000
Jan 118 135 287 38745

and

Product Code Quantity in Stock Cost Price
112 2000 250
116 3000 150
118 1500 128
119 4000 145
120 2000 148

now I have to Calculate Profit and loss in terms of amount and % using nested loops

can you please assist me
Thanks
Reply
Jeyner Lopez on June 12, 2019 at 4:16 pm

Hi, thanks for everything. I don’t see nowhere mention a loop to repeat formulas or application like coefficient trendline and control the x and y, or looping average, or looping forecasting, it is possible to talk about this.
PLEASE.
THANK YOU SIR.
Reply
Yeshu on July 5, 2019 at 1:54 pm

Hi Paul,
Thank you for all the great information above. I have a assignment to do on every month.

There are a set of excel file which need to be consolidated into one workbook and all are in sheet1. It will be very helpful if you provide your assistance. i have tired many VBA codes but still not successful. I’m very new to the VBA.

The below code go to the folder path and update the file name in destination workbook called as “zmaster” and paste in consolidation sheet. I don’t want name to be updated what i want is i need the data which is updated in each input files from column A to P.

Sub folder()

Dim wb As ThisWorkbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Worksheets(“consolidation”)
Dim rownum As Integer
rownum = 1

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim fol As folder
Set fol = fso.GetFolder(“C:\Users\Y\Favorites\Downloads\Testing\”)
Dim myfile As File
For Each myfile In fol.Files
ws.Cells(rownum, 1).Value = myfile.Name
rownum = rownum + 1
Next
End Sub
Reply
Hongjie Lin on August 16, 2019 at 6:00 am

For i = 1 To 1000
If KL.Cells(i, 1) = “(Serial No.” Then
Dim rg As Range
Set rg = KL.Cells(i, 1).CurrentRegion
Dim cell As Range
For Each cell In rg
If Val(cell) > 0 Then
count = count + 1
End If
Next cell
Debug.Print count
count = 0

End If
Next i

Hi Paul, I am doing a count procedure where i will check each individual parts of the excel file. Problem now is that i want to write the count (continuously) after finishing each part to a place in the excel file whereby the rows is not the same. Any Idea how i go about doing it
Reply
Kumar on March 2, 2020 at 5:01 pm

How to Loop in sheet 1 step by 1 and in second sheet step by 2 simultaneously

Thanks
Kumar
Reply
Baseam on May 31, 2020 at 8:21 am

Please , i have this problem, my code
for i = 1 to 100
if worksheets(“sheet1”).cells(i,1).value = i ————this dont work
when i change i to 1 it works like this
if worksheets(“sheet1”).cells(i,1).value = 1———–work
i try convert the cell covert the every value no way , thanks
Reply
- Paul Kelly on June 2, 2020 at 2:59 am
  
  What do you mean “doesn’t work”? What is the error?
  Reply
  - Baseam on June 2, 2020 at 11:05 am
    
    there is no error , the code doesnt execute , i copy certain cells form one sheet to another based on two certaria this is one of them , when used i variable the code copy nothing , when i hand write “1” it copy the cell that its value “1” but the file has handreds of cells , thanks
    Reply
Shijo Xavier on June 19, 2020 at 3:36 am

Hi Paul
My knowledge in VBA is too bad. Just want to have a help from you. I am trying to run a number of employees in a Row (starting from A2 to the last cell with the value, no blank cell meanwhile). For some cases, I get error while running a cell value and I want to skip that error and run with the next cell.
Currently, I am using the codes
Dim Rng As Range
Set Rng = Range(“A2”, Range(“A2”).End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
For Each cell In Rng
my codes here and then
I am aware about the line where I can get an error, so I use the code as
On Error GoTo Errhandler
once I furnished with codes here
Errhandler:
and my codes updated here and then
Exit Sub
and again want to continue with the next cell but it is failed and stopping it there.
Please help me.
Thanks
Reply
herbet on August 30, 2020 at 5:21 pm

i guess its format of cells(i,1).value

just idea please try :
if value(worksheets(“sheet1”).cells(i,1).value)=i —-

herbet
Reply
Jeyner Lopez on September 9, 2020 at 11:11 pm

Thank you Paul for all the great job you share with Us.
Reply
- Paul Kelly on September 17, 2020 at 2:23 am
  
  Thanks Jeyner
  Reply
Antoine on September 22, 2020 at 4:07 pm

Hi Paul, here’s my question. How to solve this :
Write a sub procedure to calculate the sum of integers from 1 to 10 000 and display the sum in the immediate window every 1000 steps.
Thanks
Reply
Morton Ellis Wakeland on February 1, 2021 at 12:45 am

Paul, Mort in Dallas. Your explanations are simply awesome! However, my 73 year old brain cells just cannot seem to comprehend some things. Am comparing two cells on same row i,3 and i,8
if i,3 will a certain year, and i,8 is > a certain date then I want to color that cell green.
code is If Cells(i,3) = intYrOne And cells (i,8) > strDueDate1 then color cell i,8
Cells (i,8).interior.colorIndex = 4
Does not work.
Apologize to bother, but if you have time for an ole man – would appreciate!
Mort Wakeland, Dallas, TX
Reply
- Paul Kelly on February 6, 2021 at 3:15 am
  
  Hi Mort,
  
  What do you mean by “does not work”? Are you getting an error or is the result not what you are expecting?
  
  If you step through the code and look at the variables in the watch window you should be able to see the issue.
  
  -Paul
  Reply
Neil on August 28, 2021 at 11:49 pm

Lets say I have a text file like so.
9 0 3 1 3 5
4 7 5 2 3 5
1 3 8 0 2 6
0 2 3 7 2 3
4 9 0 9 8 8
4 6 2 7 8 0
7 8 6 9 0 4
3 0 4 5 1 8
0 1 8 1 2 3
1 3 7 8 3 8
7 7 0 5 6 8
9 0 3 2 0 5
6 8 2 4 9 7
so my last line will look like -3 +8 -1 +2 +9 +2
I want to iterate place all the numbers into an array or I might not have to and us a
do while !EOF
loop through each column and compare it to the number in the same row of the next column and write the difference into another text file or another worksheet, any idea how i could do this?
Reply

VBA For Loop – A Complete Guide

Related Links for the VBA For Loop

A Quick Guide to the VBA For Loop

The VBA For Loop Webinar

Introduction to the VBA For Loop

What are VBA For Loops?

VBA For Loop Example 1

The Immediate Window

VBA For Loop Example 2

VBA For Loop Example 3

Advantages of the VBA For Loop

The Standard VBA For Loop

YouTube Video For Loop

Format of the Standard VBA For Loop

How a For Loop Works

Using Step with the VBA For Loop

Exit the For Loop

Using the VBA For Loop with a Collection

Using Nested For Loops

The VBA For Each Loop

Format of the VBA For Each Loop

Order of Items in the For Loop

Using the VBA For Each Loop With Arrays

Using Nested For Each Loops

How to Loop Through a Range

Summary of the VBA For Loops

The Standard VBA For Loop

The VBA For Each Loop

What’s Next?

40 Comments

Submit a Comment Cancel reply

You are not logged in