Tuesday, March 22, 2005

Loop Trough Cells in Excel and Change the Value of an Adjacent Cell

There is an Excel spreadsheet with two columns. Column A contains customer names and column B contains the number of invoices billed. Whenever the cell in column B is empty, I want to input a Zero. If the cell in column B is not empty, then I do not want to modify the existing value.
The loop will stop when it reaches an empty cell in column A.


Sub LoopThruCells()
[A1].Select
intRow = 0
Do While ActiveCell.Offset(intRow, 0).Value <> ""
If ActiveCell.Offset(intRow, 1).Value = "" Then
ActiveCell.Offset(intRow, 1).Value = 0
Else
ActiveCell.Offset(intRow, 1).Value = ActiveCell.Offset(intRow, 1).Value
End If
intRow = intRow + 1
Loop
End Sub

No comments: