Цикл For Each… Next в VBA Excel, его синтаксис и описание отдельных компонентов. Примеры использования цикла For Each… Next.
Цикл For Each… Next в VBA Excel предназначен для выполнения блока операторов по отношению к каждому элементу из группы элементов (диапазон, массив, коллекция). Этот замечательный цикл применяется, когда неизвестно количество элементов в группе и их индексация, в противном случае, более предпочтительным считается использование цикла For…Next.
Синтаксис цикла For Each… Next
|
1 2 3 4 5 |
For Each element In group [ statements ] [ Exit For ] [ statements ] Next [ element ] |
В квадратных скобках указаны необязательные атрибуты цикла 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 выделите диапазон ячеек и запустите на выполнение следующую процедуру:
|
1 2 3 4 5 6 7 8 9 |
Sub test1() Dim element As Range, a As String a = "Данные, полученные с помощью цикла For Each... Next:" For Each element In Selection a = a & vbNewLine & "Ячейка " & element.Address & _ " содержит значение: " & CStr(element.Value) Next MsgBox a End Sub |
Информационное окно MsgBox выведет адреса выделенных ячеек и их содержимое, если оно есть. Если будет выбрано много ячеек, то полностью информация по всем ячейкам выведена не будет, так как максимальная длина параметра Prompt функции MsgBox составляет примерно 1024 знака.
Цикл для коллекции листов
Скопируйте следующую процедуру VBA в стандартный модуль книги Excel:
|
1 2 3 4 5 6 7 8 9 |
Sub test2() Dim element As Worksheet, a As String a = "Список листов, содержащихся в этой книге:" For Each element In Worksheets a = a & vbNewLine & element.Index _ & ") " & element.Name Next MsgBox a End Sub |
Информационное окно MsgBox выведет список наименований всех листов рабочей книги Excel по порядковому номеру их ярлычков, соответствующих их индексам.
Цикл для массива
Присвоим массиву список наименований животных и в цикле For Each… Next запишем их в переменную a. Информационное окно MsgBox выведет список наименований животных из переменной a.
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub test3() Dim element As Variant, a As String, group As Variant group = Array("бегемот", "слон", "кенгуру", "тигр", "мышь") 'или можно присвоить массиву значения диапазона ячеек 'рабочего листа, например, выбранного: group = Selection a = "Массив содержит следующие значения:" & vbNewLine For Each element In group a = a & vbNewLine & element Next MsgBox a End Sub |
Повторим ту же процедуру VBA, но всем элементам массива в цикле For Each… Next присвоим значение «Попугай». Информационное окно MsgBox выведет список наименований животных, состоящий только из попугаев, что доказывает возможность редактирования значений элементов массива в цикле For Each… Next.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub test4() Dim element As Variant, a As String, group As Variant group = Array("бегемот", "слон", "кенгуру", "тигр", "мышь") 'или можно присвоить массиву значения диапазона ячеек 'рабочего листа, например, выделенного: group = Selection a = "Массив содержит следующие значения:" & vbNewLine For Each element In group element = "Попугай" a = a & vbNewLine & element Next MsgBox a End Sub |
Этот код, как и все остальные в этой статье, тестировался в Excel 2016.
Цикл для коллекции подкаталогов и выход из цикла
В этом примере мы будем добавлять в переменную a названия подкаталогов на диске C вашего компьютера. Когда цикл дойдет до папки Program Files, он добавит в переменную a ее название и сообщение: «Хватит, дальше читать не буду! С уважением, Ваш цикл For Each… Next.».
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Sub test5() Dim FSO As Object, myFolders As Object, myFolder As Object, a As String 'Создаем новый FileSystemObject и присваиваем его переменной "FSO" Set FSO = CreateObject("Scripting.FileSystemObject") 'Извлекаем список подкаталогов на диске "C" и присваиваем 'его переменной "myFolders" Set myFolders = FSO.GetFolder("C:\") a = "Папки на диске C:" & vbNewLine 'Проходим циклом по списку подкаталогов и добавляем в переменную "a" 'их имена, дойдя до папки "Program Files", выходим из цикла For Each myFolder In myFolders.SubFolders a = a & vbNewLine & myFolder.Name If myFolder.Name = "Program Files" Then a = a & vbNewLine & vbNewLine & "Хватит, дальше читать не буду!" _ & vbNewLine & vbNewLine & "С уважением," & vbNewLine & _ "Ваш цикл For Each... Next." Exit For End If Next Set FSO = Nothing MsgBox a End Sub |
Информационное окно MsgBox выведет список наименований подкаталогов на диске C вашего компьютера до папки Program Files включительно и сообщение цикла о прекращении своей работы.
В результате работы программы будут выведены не только наименования подкаталогов, видимых при переходе в проводнике к диску C, но и скрытые и служебные папки. Для просмотра списка всех подкаталогов на диске C, закомментируйте участок кода от If до End If включительно и запустите выполнение процедуры в редакторе VBA Excel.
Здравствуйте!
Я новичок в VBA, поэтому ,возможно, вопрос глупый, но не могу никак придумать процедуру для сравнения двух массивов с помощью цикла For Each… Next.
т.е. имеются столбцы A:B — ФИО1 Дата1 и столбцы C:D — ФИО2 Дата2. Хочу сравнить и при совпадении выделить совпадающие ячейки цветом.
Подскажите, хотя бы примерно, как это сделать.
Спасибо
Здравствуйте!
Диапазоны в примере одинаковые по размеру, но могут быть любые:
Спасибо большое, Евгений!
Я немного неправильно сформулировал.
Нужно выделить, если пары ФИО и Дата совпадают.
т.е. Иванов 1979 и Иванов 1979 выделялись, а Иванов 1979 и Иванов 1980 нет. Так же как и Иванов 1979 и Петров 1979 не нужно выделять.
Пока придумал только если создать дополнительные столбцы и в них прописать «=A1&B1» и по ним сравнивать.
Можно ли как то сделать по другому?
Можно и по-другому, попробуйте так:
Спасибо огромное. То, что нужно!
Здравствуйте!
Подскажите пожалуйста, как с помощью цикла For Each…Next можно выполнить задачу:
Есть диапазон со значениями, например («B1:С5»)
Нужно все значения из этого диапазона последовательно вынести в столбец A (раздельно, не в одну ячейку)
Здравствуйте, Дарья!
Большое спасибо!
У вас очень классный сайт, существенно мне помог разобраться в VBA Excel
Добрый день, очень часто пользуюсь информацией с Вашего сайта, но вот тут возникла задача, с которой справиться не получается, возможно вы сможете помочь.
У меня есть массив:
В данном массиве необходимо сравнить каждую ячейку с каждой из этого же массива и найти такую же цифру, только с противоположным знаком, если такой цифры с противоположным знаком нет -> (то вывести в столбик такие цифры, к которым нет противоположных), а если есть цифра с противоположным знаком -> (то ничего делать с ней не нужно).
Здравствуйте, Михаил!
Найденные числа записываются в первый столбец листа «Лист1». Пустые ячейки (нули) игнорируются.
Добрый вечер! Подскажите, пожалуйста, как исправить проблему? Дана строка символов, и нужно определить, встречаются ли в ней 5 символов «$» и 3 символа «%»? Почему-то в любом случае результат, что «Символы не встречаются». Заранее спасибо.
Дарья, объявление переменных
соответствует объявлению
А код измените следующим образом:
Здравствуйте.
Подскажите, пожалуйста, как выполнить задачу. Есть лист1. В нем столбик А с ячейками ID(заранее известно количество строк). Нужно сравнить с ячейками ID столбика А листа2 и при совпадении записать данные из столбика К листа1 в столбик D листа2 вместе с цветом ячейки. В листе2 не известно заранее сколько строк. Я написала начало, дальше не пойму как.
Здравствуйте, Елена!
Как я понял из вашего кода, просмотр начинается с 3 строки. Вот два варианта решения, где вам нужно только заменить Лист11 на Worksheets("22.11-28.11") и Лист12 на второй ваш лист:
Спасибо большое! Первый вариант мне как-то более понятен. Т.е. теперь, чтобы передать цвет ячейки, надо дописать перед выходом из внутреннего цикла
Только это не работает.
Елена, вы правильно написали, надо добавить именно эту строку, и она работает — только что проверил. Может быть, вы в ней не поменяли названия листов?
Да работает. Просто цвета, созданные условным форматированием, не переносятся
Попробуйте
заменить на
Результат не гарантирован, так как он будет зависеть от формулировок правил условного форматирования.
Как-то частично переносит цвет.
Заметила, что и на первом листе и на втором игнорируется последняя строка. Поставила n1 + 1, n2 + 1 и все работает как надо.
Подскажите, пожалуйста,как обозначить выполнение цикла операций после проведения отбора каждого из возможных значений по одному из столбцов?
Есть массив данных(много столбцов) с разными значениями в ячейках столбца День(от 1до 31). Нужно последовательно для каждого дня вывести массив, скопировать его (отдельные части) и перенести в другую книгу. Так для каждого дня. Т.е последовательно скопировать и вставить массивы данных в другую книгу. В общем массиве могут быть не все даты месяца.