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
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 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:
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
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.)
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
Thanks K-Li-Ch,
I’ve updated the code to correct that error.
Paul
Yes it would be a better way to write it.
Or “While” a condition is met. Same with Do … Loop While.
Sincerely,
K-Li-Ch
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?
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
Hi Paul,
Is there a way to create n for loops without typing manually every loop?
Thanks
Hi Nicolas.
No. There is no mechanism for this in VBA.
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
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
Worksheets(1).Range(“$A$1”).Value
Worksheets(2).Range(“$A$1”).Value
Great Content.
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.
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
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.
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.
Hi Mireia,
Sure. You can do this
Paul
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!
Hi Austin,
You can use a loop with the Application.Wait function. This function will pause to code for a specific amount of time.
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
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
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
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
You can use “Exit For” to exist the loop
If Instr(.Cells(i, j).Value,”\\C:myfolder”) > 0 Then
Exit For
End If
Paul
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
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.
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
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
How to Loop in sheet 1 step by 1 and in second sheet step by 2 simultaneously
Thanks
Kumar
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
What do you mean “doesn’t work”? What is the error?
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
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
i guess its format of cells(i,1).value
just idea please try :
if value(worksheets(“sheet1”).cells(i,1).value)=i —-
herbet
Thank you Paul for all the great job you share with Us.
Thanks Jeyner
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
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
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
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?