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:

2 Comments on this post

Trackbacks

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

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives