Условия и циклы

Учим макрос принимать решения и повторять работу: If, Select Case, циклы For, For Each, Do While — и главный рабочий приём всей книги: обход строк таблицы.

Цикл — конструкция, которая повторяет один и тот же блок кода нужное число раз, каждый раз подставляя новые данные: следующую строку, следующую ячейку, следующий лист.

Зачем это нужно

Вся ценность VBA сводится к одной фразе: «пройди по всем строкам и сделай с каждой то же, что я делаю руками». Отчёт на 3000 строк, где надо проставить статус, подкрасить просрочку и удалить пустые записи, руками занимает полдня. Макрос из 20 строк делает это за две секунды. Условия и циклы — ровно те два кирпичика, из которых такой макрос собирается.

If: развилка

Условие проверяет, верно ли утверждение, и выполняет разные ветки кода. Классифицируем сделки по сумме:

Option Explicit

Sub KategoriyaSdelki()
    Dim summa As Double
    summa = Range("B2").Value

    If summa >= 100000 Then
        Range("C2").Value = "Крупная"
    ElseIf summa >= 10000 Then
        Range("C2").Value = "Средняя"
    Else
        Range("C2").Value = "Мелкая"
    End If
End Sub

Разбор по строкам. If ... Then — проверка. Ветки ElseIf проверяются сверху вниз, до первого сработавшего; остальные пропускаются. Else — «во всех прочих случаях». End If закрывает конструкцию — забыть его нельзя, VBA сразу пожалуется.

Порядок веток критичен. Если поменять первые две местами, сумма 150 000 попадёт в «Среднюю»: она ведь тоже >= 10000, а проверка остановится на первом совпадении. Правило простое: от самого узкого условия к самому широкому.

Операторы сравнения: = (равно), <> (не равно), >, <, >=, <=. Соединяются словами And, Or, Not. Короткий If в одну строку пишется без End If и хорош для ранних выходов:

If Range("B2").Value = "" Then Exit Sub   ' нет данных — уходим

Select Case: когда веток много

Пять ElseIf подряд читаются тяжело. Для проверки одного значения на много вариантов есть Select Case:

Sub KoefficientRegiona()
    Dim region As String
    Dim koef As Double

    region = Range("D2").Value

    Select Case region
        Case "Москва", "Санкт-Петербург"
            koef = 1.2
        Case "Урал", "Сибирь"
            koef = 1.15
        Case "Дальний Восток"
            koef = 1.3
        Case Else
            koef = 1
    End Select

    Range("E2").Value = koef
End Sub

Select Case умеет и диапазоны — это часто выручает:

Select Case summa
    Case Is >= 100000
        kategoriya = "Крупная"
    Case 10000 To 99999.99
        kategoriya = "Средняя"
    Case Else
        kategoriya = "Мелкая"
End Select

Три вида циклов

For ... Next — когда знаем, сколько повторов

Dim i As Long

For i = 2 To 10
    Cells(i, 3).Value = Cells(i, 1).Value * Cells(i, 2).Value
Next i

i — счётчик: он сам получает значения 2, 3, 4 … 10. Шаг задаётся словом Step: For i = 2 To 100 Step 2 пройдёт только чётные, а Step -1 пойдёт снизу вверх (это нам скоро понадобится).

For Each — по коллекции

Когда нужно обойти «всё, что есть», не заботясь о номерах:

Dim yacheyka As Range
Dim ws As Worksheet

For Each yacheyka In Range("A2:A20")
    If yacheyka.Value = "" Then yacheyka.Value = "нет данных"
Next yacheyka

For Each ws In ThisWorkbook.Worksheets
    ws.Range("A1").Value = "Обновлено: " & Date
Next ws

Do While / Do Until — пока условие держится

Число повторов заранее неизвестно: идём вниз, пока в столбце есть значения.

Dim i As Long
i = 2

Do While Cells(i, 1).Value <> ""
    Cells(i, 4).Value = "OK"
    i = i + 1
Loop

Do Until Cells(i, 1).Value = "" — то же самое, но «пока не станет пусто». Выбирайте то, что читается естественнее. Главная опасность Do — забыть i = i + 1: условие никогда не изменится, и Excel зависнет. Аварийная остановка — Ctrl+Break (на ноутбуках часто Ctrl+ScrLk или Esc, зажатый несколько раз).

Досрочный выход

Нашли что искали — незачем крутить цикл до конца. Exit For выходит из For, Exit Do — из Do, Exit Sub — сразу из всей процедуры.

Sub NaytiKlienta()
    Dim i As Long
    Dim nayden As Boolean

    For i = 2 To 1000
        If Cells(i, 1).Value = "ООО Ромашка" Then
            MsgBox "Найдено в строке " & i
            nayden = True
            Exit For
        End If
    Next i

    If Not nayden Then MsgBox "Клиент не найден"
End Sub

Главный приём: обход строк таблицы

Это тот самый шаблон, который вы будете писать в 9 макросах из 10. Он состоит из двух частей: сначала находим последнюю заполненную строку, потом идём по ней циклом.

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Читается справа налево: возьми самую нижнюю ячейку столбца A (Rows.Count — это 1 048 576 на современном листе), нажми «Ctrl+Стрелка вверх» (End(xlUp)) и скажи, в какой строке ты оказался (.Row). Ровно то, что вы делаете руками, чтобы допрыгнуть до конца таблицы.

Пусть на листе «Заказы» такая таблица:

A            B          C            D         E
Номер        Клиент     Срок         Статус    Оплата
1001         Ромашка    10.07.2026             Оплачен
1002         Василёк    05.07.2026             Не оплачен

Задача: пометить в столбце D просроченные и неоплаченные заказы и подсветить их строку.

Option Explicit

Sub PometitProsrochku()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim srok As Date

    Set ws = ThisWorkbook.Worksheets("Заказы")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow                       ' 2 — потому что строка 1 это шапка
        If Not IsDate(ws.Cells(i, 3).Value) Then
            ws.Cells(i, 4).Value = "Нет срока"
        Else
            srok = ws.Cells(i, 3).Value

            If srok < Date Then
                If ws.Cells(i, 5).Value <> "Оплачен" Then
                    ws.Cells(i, 4).Value = "Просрочен"
                    ws.Rows(i).Interior.Color = RGB(255, 220, 220)
                End If
            End If
        End If
    Next i

    MsgBox "Обработано строк: " & (lastRow - 1)
End Sub

Разберём ключевые места. Set ws = ... привязывает макрос к конкретному листу — он отработает правильно, даже если запустить его с другого листа. IsDate отсеивает строки с мусором вместо даты: без этой проверки первая же ячейка с текстом уронит макрос. Date без скобок — встроенная функция «сегодня». А два вложенных If вместо одного с And — не стилистика, а необходимость: см. «Как это работает».

Удаление строк — только снизу вверх

Классическая ловушка. Удаляем пустые записи:

For i = lastRow To 2 Step -1
    If ws.Cells(i, 2).Value = "" Then ws.Rows(i).Delete
Next i

Почему Step -1? Когда вы удаляете строку 5, бывшая строка 6 становится пятой. Цикл, идущий сверху вниз, на следующем шаге посмотрит на строку 6 — и пропустит ту, что «подъехала» на её место. Две пустые строки подряд превращаются в одну оставшуюся. Идя снизу вверх, вы сдвигаете только уже пройденное — и ничего не теряете.

Приём для больших таблиц: массив вместо ячеек

50 000 обращений Cells(i, 2).Value — это 50 000 «разговоров» с Excel, и они дорогие. Можно забрать весь столбец в память одним махом:

Dim dannye As Variant
Dim i As Long
Dim itogo As Double

dannye = ws.Range("B2:B50001").Value   ' один обмен вместо 50 000

For i = 1 To UBound(dannye, 1)
    If IsNumeric(dannye(i, 1)) Then itogo = itogo + dannye(i, 1)
Next i

MsgBox "Итого: " & itogo

dannye становится двумерным массивом (строки × столбцы), нумерация с 1, UBound(dannye, 1) — число строк в нём. На типичном отчёте такой переход даёт ускорение в десятки раз.

Как это работает

Граница цикла вычисляется один раз. В For i = 2 To lastRow значение lastRow берётся на входе в цикл. Если внутри вы удалите строки и пересчитаете lastRow, цикл этого не заметит — он уже знает, до скольких считать. Именно поэтому удаление делают обратным циклом, а не «подкручиванием» границы.

VBA не делает короткого замыкания. В большинстве языков If A And B не проверяет B, если A уже ложно. VBA проверяет обе части всегда. Поэтому строка вида If IsDate(c.Value) And c.Value < Date Then честно попытается сравнить текст с датой даже тогда, когда IsDate вернул False, — и упадёт с Type mismatch. Выход — вложенные If, как в примере выше.

Каждое касание листа стоит времени. Чтение и особенно запись в ячейку идёт через межпрограммный вызов, и Excel по дороге ещё и перерисовывает экран и пересчитывает формулы. Отсюда стандартная «обвязка» для тяжёлых макросов:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' ... тут длинный цикл ...

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Частые ошибки

• Магическая граница цикла. For i = 2 To 1000 либо гоняет пустоту, либо обрезает таблицу, когда в ней стало 1200 строк. Считайте lastRow через End(xlUp).
• Удаление строк сверху вниз. Половина пустых строк останется. Только Step -1.
• Cells и Range без листа. Без ws. они работают с активным листом. Макрос, запущенный не с того листа, начнёт править чужие данные.
• Бесконечный Do. Забыли увеличить счётчик — Excel «завис». Ctrl+Break.
• Проверка на пустоту через = "", когда в ячейке пробел. Визуально пусто, для VBA — текст. Спасает Trim(...) = "".
• And в надежде на короткое замыкание. VBA вычислит обе части. Вкладывайте If друг в друга.
• Exit Sub вместо Exit For. Первый выходит из всего макроса, а не из цикла, — и «хвост» работы не выполняется.

Итоги

• If / ElseIf / Else / End If — развилка; ветки проверяются сверху вниз, до первого совпадения.
• Select Case читается лучше, когда вариантов много; умеет списки (Case "А", "Б") и диапазоны (Case Is >= 100, Case 1 To 10).
• For — когда число повторов известно, For Each — по коллекции ячеек или листов, Do While/Do Until — пока держится условие.
• Exit For, Exit Do, Exit Sub — досрочный выход.
• Шаблон обхода таблицы: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, затем For i = 2 To lastRow.
• Удаляете строки — идите Step -1, снизу вверх.
• Тормозит на больших данных — читайте диапазон в Variant-массив одним присваиванием.