Oct 12 2008

Using a For Loop in a Macro

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.

[ad#in-post-ad]

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:

  1. 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).
  2. 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.
  3. 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). 
  4. 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.
  5. 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.
  6. 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. 
  7. 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

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS:

28 Comments on this post

Trackbacks

  1. Count Rows with Data in a Macro | Excel Hints wrote:

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

    October 16th, 2008 at 9:58 pm
  1. 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
  2. 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
  3. 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 case

    Case: 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 30

    Thanks and Regards,
    Gopal

    November 22nd, 2008 at 4:36 am
  4. 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 222220400

    July 10th, 2009 at 3:59 pm
  5. 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
  6. 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
  7. 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 Sub

    I then closed module and VB and went to Macros
    I selected TestLoop and then Run but nothing happened.
    I started with my pointer at A1

    Any idea of what went wrong. Thanks.

    September 17th, 2009 at 4:31 pm
  8. 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
  9. 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
  10. 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
  11. 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 Sub

    I 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) = x

    Next x
    End Sub

    Wtih 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
  12. 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
  13. 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.000

    April 7th, 2011 at 6:43 am
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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 Sub

    first 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 A1

    Any idea of what went wrong. Thanks.

    December 13th, 2011 at 9:59 pm
  19. Ronny Brown said:

    I am trying to create a list using the same two cells across several worksheets. I have tried to record a micro, but it will only paste the data for the first worksheet and not the others. When I run the micro it appears to be gathering data from all worksheets, but it will not paste it onto the worksheet desired. Help?

    April 17th, 2012 at 8:14 am
  20. Brian H said:

    Hi, I’m trying to make a macro or a statement that will go down a list of data checking for a name, and if the name is correct, then get a data value from a cell on the same row.

    An example of the data is:

    09:20 | 22565 | A3004110 | SUZANNE.D | 5 | 0.99 | 5.99
    09:58 | 24765 | A3794897 | BRIAN.H | 8.65 | 1.73 | 10.38

    I’d like the formula / macro to check for the name in cell 4, in this case BRIAN.H and if true, get the value in cell 7

    then carry on down the list and add the next Cell 7 that matches the name.

    Is this possible?

    Thanks.

    Brian

    April 18th, 2012 at 11:58 am
  21. Ian said:

    I need to create a macro that will validate and return a value:
    Example:
    Column A Coumn B Column C
    X

    May 11th, 2012 at 5:46 pm
  22. Ian said:

    Hi,

    I need to create a macro that will return a value and paste it on column if both criteria are met. Also, the values on column a and b changes depending on the value of column c, so i need a loop to validate the data.

    Column A column B Column C
    x ok Rejected
    ok ok Approved
    x x Rejected
    ok x Rejected

    May 11th, 2012 at 5:49 pm
  23. Manas Kumar Pradhan said:

    I have 5 columns and 5500 rows data in one sheet and i have some data in another sheet. suppose i added two more columns in the first sheet to pick up some data from the second sheet through v-look up and the range of data is different in different time what should i do to put loop the ranges of data through loop code in vba and where should i put the loop code that it will work properly in each run dealing with ranges of data .

    Pleas some one add loop code for the below example:- suppose the range given below as (“G5000″) changes in next run as 7000. How can the loop code will run in such ranges of data.

    Range(“G1′).select
    Selection.AutoFilter Field:7
    Range(“G2″).Select
    Activecell.formulaR1C1=_”VLOOKUP(RC[-6],’WORKBOOK2.Xls’!c1:c8,8,0)”
    Range(“G2′).Select
    Selection.copy
    Range(“F2″).Select
    selection.End(xldown).select
    Range(‘G5000″).Select
    Range(Selection,selection.End(xlup)).select
    ActiveSheet.paste
    Application.CutCopyMOde=False
    Selection.End(XlUp).select
    Selection.Autofilter
    Columns(‘G:G”).Select
    selection.copy
    Selection.pastespecial Paste:=xlpasteValues, Operation:=xlNone, Skip Blanks_:=false, Transpose:=false
    Activesheet.paste

    July 22nd, 2012 at 1:56 pm
  24. Vijay said:

    Could you help. I’m trying to create a macro to see a table and if a particular value is then copy the content of a corresponding cell to another sheet.
    CARCASS BOARD OPTIONS Room 1 Room 2 Room 3 Room 4
    PPB-OSL x
    PPB-BSL
    MDF OSL
    MDF BSL x
    PLY OSL
    PLY BSL
    SHUTTER OPTIONS
    Prelamiante Particle board OSL x
    Prelaminate Particle board BSL
    MDF Prelam OSL
    MDF Prelam BSL
    MDF Lam OSL Std lamiante x
    MDF Lam OSL Swede lamiante

    There are total of 5 columns and I need to copy the column A data into another worksheet if there is a ‘x’ mark appearing in the corresponding column B, C, D or E. This list goes on to 140 rows and every time a new sheet is opened with the new x marks want to have worksheet showing that details of Column A.

    Hope it is clear and u can help me.

    September 22nd, 2012 at 6:39 am
  25. R said:

    hi
    i want to repeat 5 columns 1000 times. and record each of them.
    how should i write the formula?r

    December 3rd, 2012 at 10:51 am
  26. balasubramanyam said:

    hi
    Could you please help me out the for below text.how to split the date from the below text using the either macros or excel formulas
    Isssue invoice on 10.01.2013 Annual Invoice 30 Days Net.
    PYT: 30 days, Please add PO# on the invoice, please create invoice with date 01.01.2013
    Re-bill for invoice no. 11202091. one annual invoice. please invoice immediately. payment terms 5 days net. Please send preview to SAM.
    future invoicing- issue the invoice on 11-Jan-2013. payment term 30 days and “send for review”.
    re-bill for order ID 2129, annual invoice, PYT: 15 days, Please add given PO# on the invoice
    30 days pt, please send preview.
    30 days pt, please send preview.
    PYT: 30 days (Please add given PO# on the invoice), please raise invoice on 15.01.2013
    Please issue invoice at 20.12.2012 PYT: 30 days
    Please bill internally; don’t send any invoice to the partner. It’s just to grant access to InforXtreme.
    one annual invoice. please invoice on 20.12.2012. payment terms 45 days net.
    PT is 30 days. Please choose the English language format. Please add the following comment to the invoice: Support and maintenance for 145 cc users BaanIV, 115 cc users BaanIV Dimensions and 10 cc users BaanIV Development Tools for the period January 1, 2013 till December 31, 2013.
    PT: 30
    30 days payment terms. Please create invoice on 15.01.2013.
    Please issue invoice at 8.1.2013 PT-30 days
    Issue invoice on 07.01.2013 Annual Invoice 30 Days Net
    Please see following billing instructions: • To bill January 1st 2013 • Payment terms 60 days • Use details with comments and group by Agreement number • Remove 0$ lines • Send me the draft for review • When applicable, include “Lawson Renewal letter” when invoice is sent by post to the customer
    Please see following billing instructions: • To bill January 1st 2013 • Payment terms 30 days • Use details with comments and group by Agreement number • Remove 0$ lines • Send me the draft for review • When applicable, include “Lawson Renewal letter” when invoice is sent by post to the customer
    Please see following billing instructions: • To bill January 1st 2013 • Payment terms 30 days • Use details with comments and group by Agreement number • Remove 0$ lines • Send me the draft for review • When applicable, include “Lawson Renewal letter” when invoice is sent by post to the customer
    Please see following billing instructions: • To bill January 1st 2013 • Payment terms 30 days • Use details with comments and group by Agreement number • Remove 0$ lines • Send me the draft for review • When applicable, include “Lawson Renewal letter” when invoice is sent by post to the customer
    Please see following billing instructions: • To bill January 1st 2013 • Payment terms 30 days • Use details with comments and group by Agreement number • Remove 0$ lines • Send me the draft for review • When applicable, include “Lawson Renewal letter” when invoice is sent by post to the customer

    December 26th, 2012 at 1:06 pm
  27. Leigh Rhodes said:

    Help me, please.

    I can run a macro to test for a desired outcome, but I can’t get it to stop at the desired point.

    I have everything set up such that I only need to execute the “calculate now” command within the macro and a range of random generators will seek out my desired outcome. But, once the best outcome is obtained (or even an interim “best”), I need to copy that result (merely a row of 11 cells and their ‘true’ or ‘false’ values) to another location.

    How can I interrupt the loop so as to extract the result when that target is met?

    In terms of functionality, the only critical elements I need are: “calculate now” and “copy and paste” – or, if not, just “STOP” at the critical moment.

    This has been driving me crazy!

    March 18th, 2013 at 9:44 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories