Microsoft Office Tutorials and References
In Depth Information
This example introduces the Cells range method to refer to a cell object. You
are already familiar with the Range(“A1”) notation, but the Cells method
offers more flexibility in VBA when referring to individual cells and ranges.
The Cells method has two arguments: the first argument is row number,
and the second argument is column number. The syntax is ParentObject
.Cells(RowIndex, ColumnIndex) . For example, the notation Cells(2, 5)
is the same as Range(“E2”) because for cell E2, column E is also regarded by
Excel as column 5, and the numeral 2 in “E2” refers to row 2. You’ll be seeing
an increased use of the Cells method in this topic because it is such an easier
and more efficient method of referring to dynamic ranges in VBA.
To have VBA test the conditional statement after executing the commands within the loop, you
simply place the conditional statement after the Loop keyword. The Do…Loop…While syntax is:
Command statements to be executed within the loop.
Loop While condition
When VBA executes the command(s) in a Do…Loop…While structure, it does so first, and then at the
Loop While line, it tests the logical condition. If the condition is True at that point, the loop iterates
again, and so on, until the condition evaluates to False.
A common request is to locate all cells in a worksheet that contain a particular value, similar to
clicking the Find Next button on the Find dialog box, and then do something to that cell or to the
cells around it. Suppose you have a worksheet filled with data and you want to find all cells that
contain the word “Hello.” These cells can be in any row or column.
For each of those cells where “Hello” is found, you want to place the word “Goodbye” in the cell of
the column to the immediate right. The following macro does just that, using a Do…Loop…While
construction that finds every cell containing “Hello” and identifies its address, so the loop can perform
only as many iterations as there are cells containing “Hello”:
Dim HelloCell As Range, BeginningAddress As String
Set HelloCell = ActiveSheet.UsedRange.Find(“Hello”, LookIn:=xlValues)
If Not HelloCell Is Nothing Then
BeginningAddress = HelloCell.Address
HelloCell.Offset(0, 1).Value = “Goodbye”
Set HelloCell = ActiveSheet.UsedRange.FindNext(HelloCell)
Loop While Not HelloCell Is Nothing And HelloCell.Address<>BeginningAddress