3.6.4. Организация циклов для перебора ячеек диапазона

Есть несколько способов перебора ячеек диапазона в цикле. Примеры из этого раздела демонстрируют использование с этой целью операторов For Each...Next и Do...Loop.

3.6.4.1. For Each...Next

Предпочтительный способ перебора ячеек диапазона — организация цикла For Each...Next; этот цикл рекомендуется и при переборе элементов наборов.

Вот как строится цикл, в котором программа перебирает ячейки диапазона A1:D10 на листе Sheet1 и обнуляет ячейки с абсолютными значениями, меньшими 0.01:

Sub RoundToZero() For Each r In Worksheets("Sheet1").Range("A1:D10").Cells If Abs(r.Value) < 0.01 Then r.Value = 0 End If Next r End Sub

Допустим, Вы хотите изменить этот код так, чтобы он обрабатывал ячейки диапазона, выделенного пользователем. Для этого подходит метод InputBox, который позволяет предложить пользователю выделить диапазон ячеек. Метод InputBox возвращает объект Range, представляющий выделенный диапазон. Используя аргумент Type и обработку ошибок, Вы сможете гарантировать выделение допустимого диапазона до того, как диалоговое окно будет убрано с экрана.

Sub RoundToZero() Worksheets( "Sheet 1") .Activate On Error GoTo PressedCancel Set r = Application.lnputBox(prompt:= "Select a range of cells", Type:=8) On Error GoTo 0

For Each с In r.Cells

If Abs(c.Value) < 0.01 Then

c.Value = 0

End If

Next с

Exit Sub

PressedCancel:

Resume

End Sub

Если выделения диапазона не требуется, то для получения объекта Range можно воспользоваться свойством CurrentRegion или UsedRange. Например, или Вы знаете, что данные на листе Sheet1 начинаются с ячейки А1 и не содержат пустых строк или колонок, то свойство CurrentRegion позволяет автоматически получить весь диапазон:

Sub RoundToZero() Set r = Worksheets("Sheet1").Range("A1").CurrentRegion For Each с In r.Cells If Abs(c.Value) < 0.01 Then c.Value = 0 End If Next с End Sub

Следующие примеры демонстрируют два способа скрыть каждую вторую Колонку в использованном диапазоне на листе Sheet1. Первый построен на цикле For Each...Next, в котором проверяется значение свойства Column объектной переменной:

Sub HideColumns() Set r = Worksheets("Sheet1").UsedRange For Each col In r.Columns If col.Column Mod 2 = 0 Then col.Hidden = True End If Next col End Sub

А второй — на цикле For...Next с проверкой счетчика цикла:

Sub HideColumns() Set r = Worksheets("Sheet1").UsedRange For i = 1 To r.Columns.Count If i Mod 2 = 0 Then r.Columns(i).Hidden = True End If Next i End Sub

3.6.4.2. Do ...Loop

В некоторых случаях цикл For Each...Next не самый удачный способ перебора ячеек диапазона. Допустим, Вам нужен макрос, который сортирует колонку и удаляет строки с одинаковыми данными. Можно было бы попробовать Применить цикл For Each...Next:

Sub BuggyRemoveDuplicates() ' HE ИСПОЛЬЗУЙТЕ ЭТОТ КОД! Worksheets ("Sheet1").Range ("А1") .Sort _ key1:= Worksheets( "Sheet1 ").Range( "A1") Set r = Worksheets("Sheet1").Range("A1").CurrentRegion.Columns("A") For Each с In r.Cells If c.Offset(1, 0).Value = c.Value Then c.Offset(1, 0).EntireRow.Delete End If Next c End Sub

Увы, этот код работает неправильно, так как метод Delete изменяет диапазон, к которому обращается цикл For Each...Next. В итоге программа не сможет удалить некоторые дубликаты данных. Лучше использовать цикл Do...Loop:

Sub GoodRemoveDuplicates() Worksheets("Sheet1").Range("A1").Sort _ key1 :=Worksheets( "Sheet1 ").Range( "А1 ") Set currentCell = Worksheets( "Sheet1 ").Range( "А1 ") Do While Not lsEmpty(currentCell) Set nextCell = currentCell.0ffset(1, 0) If nextCell.Value = currentCell.Value Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Loop End Sub

Этот цикл проверяет объектную переменную currentCell и завершается, дойдя до пустой ячейки под колонкой данных. Тот же эффект даст и сравнение в цикле значения currentCell с пустой строкой:

Do While currentCell.Value <> "" ' здесь обрабатываем заполненные ячейки Loop

В любом случае не забывайте переходить к следующей ячейке в конце тела цикла Do...Loop (например, Set currentCell = nextCell).