VBA Excel. Цикл For Each… Next

    Цикл For Each… Next в VBA Excel, его синтаксис и описание отдельных компонентов. Примеры использования цикла For Each… Next.

    Цикл For Each… Next в VBA Excel предназначен для выполнения блока операторов по отношению к каждому элементу из группы элементов (диапазон, массив, коллекция). Этот замечательный цикл применяется, когда неизвестно количество элементов в группе и их индексация, в противном случае, более предпочтительным считается использование цикла For…Next.

    Синтаксис цикла For Each… Next

    В квадратных скобках указаны необязательные атрибуты цикла For Each… Next.

    Компоненты цикла For Each… Next

    Компонент Описание
    element Обязательный атрибут в операторе For Each, необязательный атрибут в операторе Next. Представляет из себя переменную, используемую для циклического прохода элементов группы (диапазон, массив, коллекция), которая предварительно должна быть объявлена с соответствующим типом данных*.
    group Обязательный атрибут. Группа элементов (диапазон, массив, коллекция), по каждому элементу которой последовательно проходит цикл For Each… Next.
    statements Необязательный** атрибут. Операторы вашего кода.
    Exit For Необязательный атрибут. Оператор выхода из цикла до его окончания.

    *Если цикл For Each… Next используется в VBA Excel для прохождения элементов коллекции (объект Collection) или массива, тогда переменная element должна быть объявлена с типом данных Variant, иначе цикл работать не будет.

    **Если не использовать в цикле свой код, смысл применения цикла теряется.

    Примеры циклов For Each… Next

    Цикл для диапазона ячеек

    На активном листе рабочей книги Excel выделите диапазон ячеек и запустите на выполнение следующую процедуру:

    Информационное окно MsgBox выведет адреса выделенных ячеек и их содержимое, если оно есть. Если будет выбрано много ячеек, то полностью информация по всем ячейкам выведена не будет, так как максимальная длина параметра Prompt функции MsgBox составляет примерно 1024 знака.

    Цикл для коллекции листов

    Скопируйте следующую процедуру VBA в стандартный модуль книги Excel:

    Информационное окно MsgBox выведет список наименований всех листов рабочей книги Excel по порядковому номеру их ярлычков, соответствующих их индексам.

    Цикл для массива

    Присвоим массиву список наименований животных и в цикле For Each… Next запишем их в переменную a. Информационное окно MsgBox выведет список наименований животных из переменной a.

    Повторим ту же процедуру VBA, но всем элементам массива в цикле For Each… Next присвоим значение «Попугай». Информационное окно MsgBox выведет список наименований животных, состоящий только из попугаев, что доказывает возможность редактирования значений элементов массива в цикле For Each… Next.

    Этот код, как и все остальные в этой статье, тестировался в Excel 2016.

    Цикл для коллекции подкаталогов и выход из цикла

    В этом примере мы будем добавлять в переменную a названия подкаталогов на диске C вашего компьютера. Когда цикл дойдет до папки Program Files, он добавит в переменную a ее название и сообщение: «Хватит, дальше читать не буду! С уважением, Ваш цикл For Each… Next.».

    Информационное окно MsgBox выведет список наименований подкаталогов на диске C вашего компьютера до папки Program Files включительно и сообщение цикла о прекращении своей работы.

    В результате работы программы будут выведены не только наименования подкаталогов, видимых при переходе в проводнике к диску C, но и скрытые и служебные папки. Для просмотра списка всех подкаталогов на диске C, закомментируйте участок кода от If до End If включительно и запустите выполнение процедуры в редакторе VBA Excel.

    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    20 комментариев для “VBA Excel. Цикл For Each… Next”

    1. Здравствуйте!
      Я новичок в VBA, поэтому ,возможно, вопрос глупый, но не могу никак придумать процедуру для сравнения двух массивов с помощью цикла For Each… Next.
      т.е. имеются столбцы A:B — ФИО1 Дата1 и столбцы C:D — ФИО2 Дата2. Хочу сравнить и при совпадении выделить совпадающие ячейки цветом.
      Подскажите, хотя бы примерно, как это сделать.
      Спасибо

      1. Евгений

        Здравствуйте!
        Диапазоны в примере одинаковые по размеру, но могут быть любые:

        1. Спасибо большое, Евгений!
          Я немного неправильно сформулировал.
          Нужно выделить, если пары ФИО и Дата совпадают.
          т.е. Иванов 1979 и Иванов 1979 выделялись, а Иванов 1979 и Иванов 1980 нет. Так же как и Иванов 1979 и Петров 1979 не нужно выделять.
          Пока придумал только если создать дополнительные столбцы и в них прописать «=A1&B1» и по ним сравнивать.
          Можно ли как то сделать по другому?

          1. Евгений

            Можно и по-другому, попробуйте так:

          2. Здравствуйте!
            Подскажите пожалуйста, как с помощью цикла For Each…Next можно выполнить задачу:
            Есть диапазон со значениями, например («B1:С5»)
            Нужно все значения из этого диапазона последовательно вынести в столбец A (раздельно, не в одну ячейку)

            1. Евгений

              Здравствуйте, Дарья!

              1. Большое спасибо!
                У вас очень классный сайт, существенно мне помог разобраться в VBA Excel

            2. Добрый день, очень часто пользуюсь информацией с Вашего сайта, но вот тут возникла задача, с которой справиться не получается, возможно вы сможете помочь.
              У меня есть массив:

              В данном массиве необходимо сравнить каждую ячейку с каждой из этого же массива и найти такую же цифру, только с противоположным знаком, если такой цифры с противоположным знаком нет -> (то вывести в столбик такие цифры, к которым нет противоположных), а если есть цифра с противоположным знаком -> (то ничего делать с ней не нужно).

              1. Евгений

                Здравствуйте, Михаил!

                Найденные числа записываются в первый столбец листа «Лист1». Пустые ячейки (нули) игнорируются.

            3. Добрый вечер! Подскажите, пожалуйста, как исправить проблему? Дана строка символов, и нужно определить, встречаются ли в ней 5 символов «$» и 3 символа «%»? Почему-то в любом случае результат, что «Символы не встречаются». Заранее спасибо.

              1. Евгений

                Дарья, объявление переменных

                соответствует объявлению

                А код измените следующим образом:

            4. Здравствуйте.
              Подскажите, пожалуйста, как выполнить задачу. Есть лист1. В нем столбик А с ячейками ID(заранее известно количество строк). Нужно сравнить с ячейками ID столбика А листа2 и при совпадении записать данные из столбика К листа1 в столбик D листа2 вместе с цветом ячейки. В листе2 не известно заранее сколько строк. Я написала начало, дальше не пойму как.

              1. Евгений

                Здравствуйте, Елена!
                Как я понял из вашего кода, просмотр начинается с 3 строки. Вот два варианта решения, где вам нужно только заменить Лист11 на Worksheets("22.11-28.11") и Лист12 на второй ваш лист:

                1. Спасибо большое! Первый вариант мне как-то более понятен. Т.е. теперь, чтобы передать цвет ячейки, надо дописать перед выходом из внутреннего цикла

                  Только это не работает.

                  1. Евгений

                    Елена, вы правильно написали, надо добавить именно эту строку, и она работает — только что проверил. Может быть, вы в ней не поменяли названия листов?

                    1. Да работает. Просто цвета, созданные условным форматированием, не переносятся

                      1. Евгений

                        Попробуйте

                        заменить на

                        Результат не гарантирован, так как он будет зависеть от формулировок правил условного форматирования.

                        1. Как-то частично переносит цвет.
                          Заметила, что и на первом листе и на втором игнорируется последняя строка. Поставила n1 + 1, n2 + 1 и все работает как надо.

                      2. Наталья

                        Подскажите, пожалуйста,как обозначить выполнение цикла операций после проведения отбора каждого из возможных значений по одному из столбцов?
                        Есть массив данных(много столбцов) с разными значениями в ячейках столбца День(от 1до 31). Нужно последовательно для каждого дня вывести массив, скопировать его (отдельные части) и перенести в другую книгу. Так для каждого дня. Т.е последовательно скопировать и вставить массивы данных в другую книгу. В общем массиве могут быть не все даты месяца.

    Добавить комментарий

    Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора.