PSS ID Number: 213636
Article Last Modified on 9/11/2002
The information in this article applies to:
- Microsoft Excel 2000
- Microsoft Visual Basic for Applications
This article was previously published under Q213636
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 professionals 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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
The following code samples assume that the list has a header row starting in cell A1 and data starting in A2.
To Search a List with a Constant, Known Number of Rows
This code moves down column A to the end of the list:
Sub Test1()
Dim x As Integer
' Set numrows = number of rows of data.
NumRows = Range("A2", Range("A2").End(xldown)).Rows.Count
' Select cell a1.
Range("A2").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
' Insert your code here.
' Selects cell down 1 row from active cell.
ActiveCell.Offset(1, 0).Select
Next
End Sub
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.)
Sub Test2()
' Select cell A2, *first line of data*.
Range("A2").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub
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 two rows, with the second row indented one 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 your code here.
'
' Step down 2 rows from present location.
ActiveCell.Offset(2, 0).Select
Loop
To Search a List for a Specific Record
Sub Test3()
Dim x As String
Dim found As Boolean
' Select first line of data.
Range("A2").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
End If
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
' Check for found.
If found = True Then
Msgbox "Value found in cell " & ActiveCell.Address
Else
Msgbox "Value not found"
End If
End Sub
REFERENCES
For more information about how to use the sample code in this article, click the article number below to view the article in the Microsoft Knowledge Base:
212536 OFF2000: How to Run Sample Code from Knowledge Base Articles
For additional information about getting help with Visual Basic for Applications, please click the article number below to view the article in the Microsoft Knowledge Base:
226118 OFF2000: Programming Resources for Visual Basic for Applications
Additional query words: xl2000
Keywords: kbdtacode kbhowto kbProgramming KB213636
Technology: kbExcel2000 kbExcel2000Search kbExcelSearch kbExcelWinSearch kbVBASearch kbZNotKeyword3 kbZNotKeyword6
