VBA For Loop – A Complete Guide

VBA For Loop

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.

Related Links for the VBA For Loop

The Complete Guide to Ranges in Excel VBA.
The Complete Guide to Copying Data in Excel VBA.
VBA Do While Loop.


A Quick Guide to the VBA For Loop

Loop formatDescriptionExample
For ... NextRun 10 timesFor i = 1 To 10
For ... NextRun 5 times. i=2,4, 6 etc.For i = 2 To 10 Step 2
For ... NextRun in reverse orderFor i = 10 To 1 Step -1
    Debug.Print i
For ... NextGo through CollectionFor i = 1 To coll.Count
    Debug.Print coll(i)
For ... NextGo through arrayFor i = LBound(arr) To UBound(arr)
    Debug.Print arr(i)
Next i
For ... NextGo through 2D arrayFor 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 ... NextGo through CollectionDim item As Variant
For Each item In coll
    Debug.Print item
Next item
For Each ... NextGo through arrayDim item As Variant
For Each item In arr
    Debug.Print item
Next item
For Each ... NextGo through 2D arrayDim item As Variant
For Each item In arr
    Debug.Print item
Next item
For Each ... NextGo through DictionaryDim key As Variant
For Each key In dict.Keys
    Debug.Print key, dict(key)
Next key
Both typesExit LoopFor 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:

VBA Excel


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

  1. Find the last row with data
  2. Store the value in variable
  3. 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

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

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


Format of the Standard VBA For Loop

The Standard VBA For Loop has the following format>form

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

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

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


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"

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

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"

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

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:

  1. Get the first Workbook from the Workbooks collection
  2. Go through all the worksheets in this workbook
  3. Print the workbook/worksheet details
  4. Get the next workbooks in the collection
  5. Repeat steps 2 to 3
  6. 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.

VBA For Loop Range
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
    ' 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.)


Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook



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


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

    and it worked perfectly.

    Sincerely yours,


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

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

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

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

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

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


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


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



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


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

    1. Hi Austin,

      You can use a loop with the Application.Wait function. This function will pause to code for a specific amount of time.

  8. 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
    End Sub

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



    Base INSS: 5.200,00 Sal.Familia INSS: 0,00 Base p/ Prev.Propria(*): 0,00 Sal.Fam.Prev.Propria: 0,00

  10. Hi sir,

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

    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!
    ‘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,


    1. You can use “Exit For” to exist the loop

      If Instr(.Cells(i, j).Value,”\\C:myfolder”) > 0 Then
      Exit For
      End If


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


    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

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

  13. 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
    End Sub

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

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

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

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

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

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


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

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

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


Leave a Reply

Your email address will not be published. Required fields are marked *