XL: Sample Macro Code to Loop Through a List on a Worksheet

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SUMMARY

When you write a Microsoft Visual Basic for Applications macro, you may need to loop through a list of data on a worksheet. There are several methods for performing this task. The "More Information" section of this article contains information about methods you can use to search the following types of lists:

• A list that contains a known, constant number of rows
• A dynamic list or a list with an unknown number of rows
• A list that contains a specific record

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

To Search a List with a Constant, Known Number of Rows

This code moves down column A to the end of the list:

   ' Set numrows = number of rows of data.
   NumRows = Range(Cells(2, "a"), Cells(2, "a").End(xldown)).Rows.Count

   ' Select cell a1.
   Cells(2, "a").Select

   ' Establish "For" loop to loop "numrows" number of times.
   For x = 1 To NumRows
      '
      ' Insert code here.
      '
      ' Selects cell down 1 row from active cell.
      ActiveCell.Offset(1, 0).Select
   ' End For loop.
   Next

To Search a Dynamic List or a List with an Unknown Number of Rows

This code moves down through column A to the end of the list. (This code assumes that each cell in column A contains an entry until the end.)

   ' Select cell A2, *first line of data*.
   Cells(2, "a").Select
   ' Set Do loop to stop when an empty cell is reached.
   Do Until IsEmpty(ActiveCell)
      '
      ' Insert code here.
      '
      ' Step down 1 row from present location.
      ActiveCell.Offset(1, 0).Select
   ' End Do loop.
   Loop

   NOTE: If there are empty cells in column A throughout the data, then
   you can modify this code to account for this condition, as long as
   empty cells are a consistent distance apart. For example, if every
   other cell in column A is empty (this might occur if every 'record'
   uses 2 rows, with the second row indented 1 cell, for example), this
   loop can be modified as follows:

      ' Set Do loop to stop when two consecutive empty cells are reached.
      Do Until IsEmpty(ActiveCell) and IsEmpty(ActiveCell.Offset(1, 0))
         '
         ' Insert code here.
         '
         ' Step down 2 rows from present location.
         ActiveCell.Offset(2, 0).Select

      ' End Do loop.
      Loop

To Search a List for a Specific Record

   ' Select first line of data.
   Cells(2, "a").Select
   ' Set search variable value.
   x = "test"
   ' Set Boolean variable "found" to false.
   found = False
   ' Set Do loop to stop at empty cell.
      Do Until IsEmpty(ActiveCell)
      ' Check active cell for search value.
      If ActiveCell.Value = x Then
         found = TRUE
         ' Exit Do loop.
      Exit Do
         
      End If
   ' End Do loop.
   Loop
   ' Check for found.
   If found = True Then 
      ' If found, active cell will be that location.
      ' Add code for whatever you want to do if match is found.
   ' End if statement.
   End If

REFERENCES

For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q163435
   TITLE     : VBA: Programming Resources for Visual Basic for
               Applications