Using a For Loop in a Macro
- 19 Comment
If you’re new to macros, you might want to check out a previous post I made Familiarizing with the Visual Basic Editor and Use the Record Macro Feature. Basically, the purpose of a macro in Excel is to provide you with a way of quickly and easily modifying the values in your spreadsheet. Actually, there are many things that an Excel Macro can do that are not possible with formulas alone. So learning Visual Basic for Applications (VBA) macros can increase your ability to accomplish your goals with Excel
So back to the point of the post: how to use a for loop in a macro. The point of a for loop is to perform some action a set number of times. Lets take a look at the format of the loop and see how it can be used.
For Loop Example:
For x = 1 to 10
Cells(x,1) = “Updated”
next x
Result of Example 1: If you look at your current Excel spreadsheet cells in column 1, rows 1-10 should all have the text “Updated” in it. Let’s look more at how the macro sees this code:
- The variable x is set to 1. You can use any variable here, it does not have to be x (for example “a”, “counter”, “loopVar” are all acceptable, as long as your variable name does not conflict with a keyword in VBA).
- Variable x is then compared to the test value, which in this case was set to 10. As long as x is less than the test value, the loop will continue to run.
- Since x is less than 10 to begin with, the line, Cells(x,1) = “Updated”, is executed. This will put the string “Updated” into cell(1,1).
- After the code inside the loop is executed, x is incremented by 1 by the “Next x” line. After the first run through the loop x is now equal to 2.
- The macro now proceeds back to the top of the loop to re-evaluate x versus the test value. x is now equal to 2 so the loop continues.
- The macro continues in this fashion until x is greater than 10, so if you run this on your own you will have the string “Updated” listed 10 times on your spreadsheet.
- After x is greater than the test value, the macro will proceed to the line after Next x and continue executing the code.
Below is the code you can place in the macro to see how this works:
Sub TestLoop()
For x = 1 to 10
Cells(x,1) = “Updated”
next x
End Sub
If you have any questions or comments about the For Loop, please leave them in the comment section below.
More Excel Tips
19 Comments on this post
Trackbacks
-
Cynthia said:
I’m trying to run a macro that will go through the workbook checking for a value in column C. If there is no value in the column C, then I need to delete the row. I need this to repeat until there is no more data. Do you have any suggestions as to how to make this happen?
November 13th, 2008 at 12:55 pm -
john said:
Well there are two ways I can quickly think of to do this. It can be done with adding an additional column and using a vlookup formula to search for the values in column C. For any values that aren’t found, you can filter the column and delete those rows.
It can also be done with a VBA macro. You can have the macro loop through the column for each value and then run a delete command on the rows that are found in column C.
Does that help?
November 13th, 2008 at 1:36 pm -
Gopal said:
Hi,
I have a situation that i need to develop a macro which shift month values based on the value entered in “Month Shift” Column. following is the case, if we entered 1 under month shift then the value should move 1 month forward and should copy under Months_Factor,
If we enter 2 under Month shift Months values carry forward 2 months that means Months values copied from march.
so January is the base month.
I am new to write macros,Can any one please help me out for this caseCase: Months Months _Factor
Month Shift Jan Feb Mar April May Jan Feb Mar April May
1 10 20 30 40 50 10 20 30 40
2 10 20 30 40 50 10 20 30Thanks and Regards,
GopalNovember 22nd, 2008 at 4:36 am -
STLlove said:
I am looking for a formula that does the following: Can you help?
Adds 0100 to each cell in column A and then increases by 100 for each line after as long as the claim number is the same.Then start over with 0100 when the claim number changes.
CLM # Result
11111 111110100
11111 111110200
11111 111110300
11111 111110400
22222 222220100
22222 222220200
22222 222220300
22222 222220400July 10th, 2009 at 3:59 pm -
john said:
Hi STLlove-thanks for visiting the site…
Try someting like this maybe (I assume in the suggestion that 11111 would be in the first column and the result would be in the 2nd):
In the first cell, just use this simple formula: =A1&”0100″
And in the remaining cells, use this formula: =IF(A2=A1,A2&”0″&IF(MID(B1,6,1)=”0″,VALUE(RIGHT(B1,3))+100,VALUE(RIGHT(B1,4))+100))
This will only work if the each cell in column A is not preceeded by any blanks. The result will be text, but you can use VALUE to change back to a number.
Hope that works for you.
John
July 11th, 2009 at 12:34 am -
Navaneetha kumar said:
In first cell type : =A1&”0100?
Remaining cells : =IF(A1=A2,B1+100,CONCATENATE(A1,”0100″))July 30th, 2009 at 5:14 am -
deekarvois said:
I tried to recreate this macro. In a blank worksheet I hit ‘Alt’ F11 to open VB, then I clicked Insert Module
when the new module opened, I pasted the subroutine data for TestLoop:
Sub TestLoop()
For x = 1 to 10
Cells(x,1) = “Updated”
next x
End SubI then closed module and VB and went to Macros
I selected TestLoop and then Run but nothing happened.
I started with my pointer at A1Any idea of what went wrong. Thanks.
September 17th, 2009 at 4:31 pm -
Derek said:
I’ve just tried the same and couldn’t get it to work. Turns out you need to edit over the inverted comas as they seem to be slightly different characters. After doing that the simple macro should run properly. Its a good, simple example so thanks for this. D
September 22nd, 2009 at 5:54 am -
Misterpiz said:
Hello, I am looking for a macro that will take data from yesterday, subtract todays and give me the differece in a specific cell. I need to run this for 10 different columns, with 6 different categories (Total, Major, Critical, Minor, Internal, and Admin). I would appreciate any and all help
Thank you
October 30th, 2009 at 8:35 am -
Automobile India said:
I would like to Loop this macro until their is no more data in the report. Does anyone know how to do it?
November 1st, 2009 at 9:54 pm -
Rahil said:
I tried to recreate this macro. In a blank worksheet I hit ‘Alt’ F11 to open VB, then I clicked Insert Module
when the new module opened, I pasted the subroutine data for TestLoop:
Sub TestLoop()
For x = 1 to 10
Cells(x,1) = “Updated”
next x
End SubI then closed module and VB and went to Macros
I selected TestLoop and then Run but nothing happened.Then i Changed the string to valriable name i.e
Sub TestLoop()
For x = 1 To 10
Cells(x, 1) = xNext x
End SubWtih this it prints the value of x which starts from 1 to 10 in first column.
Appriciated the help provided, Thanks EH
March 29th, 2010 at 4:40 am -
purna chandra sahu said:
Kindly suggest me for build a macro program in excel to find a duplicate data with in excell sheet
December 27th, 2010 at 2:25 am -
TY said:
I need to write a macro that will count and return the numbers between the zeros and list them in descending order.
Any advice?
0.000
700.427
700.435
0.000
701.443
0.000
702.389
702.395
702.398
702.433
0.000
703.346
703.348
703.350
703.351
703.355
703.368
703.370
703.376
703.382
703.385
703.389
0.000April 7th, 2011 at 6:43 am -
john said:
I would need a little more detail of what you’re trying to do… list what in descending order? Do you want the results sorted?
April 7th, 2011 at 10:52 am -
Kalai said:
Hi,
I want to convert column to text using macro, Currently i am using statement like below,
Selection.Value = Selection.Offset(0, -223).Value & “;” & Selection.Offset(0, -222).Value & “;” & Selection.Offset(0,…….
But the issue is i need to write the statement for all the columns all the way from -223 to 0 and the function “&” also limited to 220 column. is there any way to get out of this.
July 18th, 2011 at 2:56 am -
mike said:
I want to copy each row 5x and then put in relevant data to each line. This may be needed for 20 lines (x4 each) or 2000 lines (x4 each) depending on the required task in hand.
Any help on this will save me many headaches.October 17th, 2011 at 12:44 pm -
Numba Wann said:
Help!!!
I would like cells (a1 b1 c1 d1) to count incrementally by 1 from 0-9 – (together they count from 0000-9999). While counting, if cell k20 = 1 or more, the counting stops. To continue counting I press “return”.
November 19th, 2011 at 11:47 am -
mohideen thasthahir said:
I tried to recreate this macro. In a blank worksheet I hit ‘Alt’ F11 to open VB, then I out the code sheet1
I pasted the TestLoop code:
Sub TestLoop()
For x = 1 to 10
Cells(x,1) = “Updated”
next x
End Subfirst am fill the valyes (” A!” to “A10″)
I then run the codes after then the value is not showing that sheet1. nothing happened.
I started with my pointer at A1Any idea of what went wrong. Thanks.
December 13th, 2011 at 9:59 pm
[...] way to count the number of rows containing data in a spreadsheet. One way would be to do use a for loop and a counting variable, but that contains unnecessary steps to get the job [...]