Использование операторов For Each. Next
Операторы For Each. Next повторяют блок операторов для каждого объекта в коллекции или каждого элемента массива. Visual Basic автоматически присваивает переменную при каждом выполнении цикла. Например, следующая процедура добавляет 10 к значению каждой ячейки в диапазоне от A1 до A10.
Sub Add10ToAllCellsInRange() Dim rng As Range For Each rng In Range("A1:A10") rng.Value = rng.Value + 10 Next End Sub Указанный ниже код перебирает все элементы массива и устанавливает для каждого из них значение переменной-счетчика I.
Dim TestArray(10) As Integer, I As Variant For Each I In TestArray TestArray(I) = I Next I Проход по диапазону ячеек
Цикл For Each. Next можно использовать для прохода по диапазону ячеек. Приведенная ниже процедура проходит по диапазону ячеек A1:D10 на листе Sheet1 и присваивает каждой ячейке, абсолютное значение которой меньше 0,01, значение 0 (ноль).
Sub RoundToZero() For Each rng in Range("A1:D10") If Abs(rng.Value) < 0.01 Then rng.Value = 0 Next End Sub Выход из цикла For Each. Next до его завершения
Из цикла For Each. Next можно выйти с помощью оператора Exit For. Например, при возникновении ошибки можно использовать оператор Exit For в блоке True оператора If. Then. Else или оператора Select Case, который используется непосредственно для проверки ошибок. Если ошибка не возникает, оператор If…Then…Else получает значение False, и цикл продолжает работу.
В следующем примере выполняется поиск первой ячейки диапазона A1:B5, не содержащей число. Если такая ячейка найдена, выводится сообщение и выполняется выход из цикла с помощью оператора Exit For.
Sub TestForNumbers() For Each rng In Range("A1:B5") If IsNumeric(rng.Value) = False Then MsgBox "Cell " & rng.Address & " contains a non-numeric value." Exit For End If Next rng End Sub Использование цикла For Each. Next для итерации в классе VBA
Для каждого. Следующие циклы не только итерируют массивы и экземпляры объекта Collection . Они также могут выполнять итерацию в созданном вами классе VBA.
Ниже приведен пример, в котором показано, как это сделать.
- Создайте модуль класса в редакторе Visual Basic (VBE) и присвойте ему имя CustomCollection. cc1
- Вставьте следующий код в только что созданный модуль.
Private MyCollection As New Collection ' The Initialize event automatically gets triggered ' when instances of this class are created. ' It then triggers the execution of this procedure. Private Sub Class_Initialize() With MyCollection .Add "First Item" .Add "Second Item" .Add "Third Item" End With End Sub ' Property Get procedure for the setting up of ' this class so that it works with 'For Each. ' ' constructs. Property Get NewEnum() As IUnknown ' Attribute NewEnum.VB_UserMemId = -4 Set NewEnum = MyCollection.[_NewEnum] End Property VERSION 1.0 CLASS BEGIN MultiUse = -1 'True END Attribute VB_Name = "CustomCollection" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = False Attribute VB_Exposed = False Private MyCollection As New Collection ' The Initialize event automatically gets triggered ' when instances of this class are created. ' It then triggers the execution of this procedure. Private Sub Class_Initialize() With MyCollection .Add "First Item" .Add "Second Item" .Add "Third Item" End With End Sub ' Property Get procedure for the setting up of ' this class so that it works with 'For Each. ' ' constructs. Property Get NewEnum() As IUnknown ' Attribute NewEnum.VB_UserMemId = -4 Set NewEnum = MyCollection.[_NewEnum] End Property Dim Element Dim MyCustomCollection As New CustomCollection For Each Element In MyCustomCollection MsgBox Element Next | Сноски | Описание |
|---|---|
| [cc1] | Вы можете создать модуль класса, выбрав Class Module (Модуль класса) в меню Insert (Вставка). Вы можете переименовать модуль класса, изменив его свойства в окне Properties (Свойства). |
| [cc2] | Вы можете активировать диалоговое окно Экспорт файла, выбрав Export File (Экспорт файла) в меню File (Файл). |
| [cc3] | Вы можете удалить модуль класса из VBE, выбрав Remove Item (Удалить элемент) в меню File (Файл). |
| [cc4] | Вы можете импортировать файл внешнего модуля класса, запустив диалоговое окно Импорт файла (выберите Import File (Импорт файла) в меню File (Файл)). |
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Циклический просмотр списка данных на листе с помощью макросов в Excel
При написании макроса Microsoft Visual Basic для приложений (VBA), возможно, придется выполнять циклический просмотр список данных на листе. Существует несколько методов выполнения этой задачи. Раздел “Дополнительная информация” этой статьи содержит сведения о методах, которые можно использовать для поиска в списках следующих типов:
- Список, содержащий известное, постоянное количество строк.
- Динамический список или список с неизвестным числом строк.
- Список, содержащий определенную запись.
Дополнительная информация
Корпорация Майкрософт предоставляет примеры программирования только в целях демонстрации без явной или подразумеваемой гарантии. Данное положение включает, но не ограничивается этим, подразумеваемые гарантии товарной пригодности или соответствия отдельной задаче. Эта статья предполагает, что пользователь знаком с представленным языком программирования и средствами, используемыми для создания и отладки процедур. Специалисты технической поддержки Майкрософт могут пояснить работу той или иной процедуры, но модификация примеров и их адаптация к задачам разработчика не предусмотрена. В следующих примерах кода предполагается, что в списке есть строка заголовка, которая начинается в ячейке A1, и данные, которые начинаются в ячейке A2.
Поиск списка с постоянным, известным числом строк
Этот код перемещает столбец А в конец списка:
Sub Test1() Dim x As Integer ' Set numrows = number of rows of data. NumRows = Range("A2", Range("A2").End(xldown)).Rows.Count ' Select cell a1. Range("A2").Select ' Establish "For" loop to loop "numrows" number of times. For x = 1 To NumRows ' Insert your code here. ' Selects cell down 1 row from active cell. ActiveCell.Offset(1, 0).Select Next End Sub
Поиск динамического списка или списка с неизвестным количеством строк
Этот код перемещает столбец А в конец списка. (Этот код предполагает, что каждая ячейка в столбце А содержит запись до конца.)
Sub Test2() ' Select cell A2, *first line of data*. Range("A2").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) ' Insert your code here. ' Step down 1 row from present location. ActiveCell.Offset(1, 0).Select Loop End Sub
Примечание Если в столбце A в данных есть пустые ячейки, измените этот код, чтобы он был указан в этом условии. Убедитесь, что пустые ячейки находятся на одинаковом расстоянии друг от друга. Например, если каждая другая ячейка в столбце А пуста (например, такая ситуация может возникнуть, если каждая запись "Record" использует две строки с отступом второй строки), этот цикл может быть изменен следующим образом:
' Set Do loop to stop when two consecutive empty cells are reached. Do Until IsEmpty(ActiveCell) and IsEmpty(ActiveCell.Offset(1, 0)) ' Insert your code here. ' ' Step down 2 rows from present location. ActiveCell.Offset(2, 0).Select Loop
Поиск списка для конкретной записи
Этот код перемещает столбец А в конец списка:
Sub Test3() Dim x As String Dim found As Boolean ' Select first line of data. Range("A2").Select ' Set search variable value. x = "test" ' Set Boolean variable "found" to false. found = False ' Set Do loop to stop at empty cell. Do Until IsEmpty(ActiveCell) ' Check active cell for search value. If ActiveCell.Value = x Then found = TRUE Exit Do End If ' Step down 1 row from present location. ActiveCell.Offset(1, 0).Select Loop ' Check for found. If found = True Then Msgbox "Value found in cell " & ActiveCell.Address Else Msgbox "Value not found" End If End Sub
Обратная связь
Были ли сведения на этой странице полезными?
Использование Do. Операторы цикла
Использовать Do. Операторы цикла для выполнения блока инструкций неограниченное количество раз. Операторы повторяются до тех пор, пока сохраняется условие True, или пока условие не станет True.
Повторяющиеся инструкции, когда условие имеет значение True
Существует два способа использования ключевого словаWhile для проверки условия в. Оператор Loop. Вы можете проверить условие перед вводом цикла или проверить его после выполнения цикла хотя бы один раз.
В следующей ChkFirstWhile процедуре необходимо проверить условие перед вводом цикла. Если myNum задано значение 9, а не 20, операторы внутри цикла никогда не будут выполняться. В процедуре ChkLastWhile инструкции в цикле выполняются только один раз, прежде чем условие станет False.
Sub ChkFirstWhile() counter = 0 myNum = 20 Do While myNum > 10 myNum = myNum - 1 counter = counter + 1 Loop MsgBox "The loop made " & counter & " repetitions." End Sub Sub ChkLastWhile() counter = 0 myNum = 9 Do myNum = myNum - 1 counter = counter + 1 Loop While myNum > 10 MsgBox "The loop made " & counter & " repetitions." End Sub Повторяющиеся инструкции до тех пор, пока условие не станет true
Существует два способа использования ключевого слова Until для проверки условия в операторе Do. Loop. Вы можете проверить условие перед вводом цикла (как показано в ChkFirstUntil процедуре) или проверить его после выполнения цикла хотя бы один раз (как показано в процедуре ChkLastUntil ). Повторение циклов продолжается, пока сохраняется условие False.
Sub ChkFirstUntil() counter = 0 myNum = 20 Do Until myNum = 10 myNum = myNum - 1 counter = counter + 1 Loop MsgBox "The loop made " & counter & " repetitions." End Sub Sub ChkLastUntil() counter = 0 myNum = 1 Do myNum = myNum + 1 counter = counter + 1 Loop Until myNum = 10 MsgBox "The loop made " & counter & " repetitions." End Sub Выход из do. Оператор Loop из цикла
Вы можете выйти из do. Выполните цикл с помощью инструкции Exit Do . Например, чтобы выйти из бесконечного цикла, используйте инструкцию Exit Do в блоке инструкции True либо if. Затем. Оператор Else или оператор Select Case . Если условие будет False, цикл запустится обычным образом.
В следующем примере myNum присваивается значение, которое создает бесконечный цикл. Оператор If. Then. Else проверяет это условие, а затем выходит из бесконечного цикла.
Sub ExitExample() counter = 0 myNum = 9 Do Until myNum = 10 myNum = myNum - 1 counter = counter + 1 If myNum < 10 Then Exit Do Loop MsgBox "The loop made " & counter & " repetitions." End Sub Чтобы остановить бесконечный цикл, нажмите клавиши ESC или CTRL+BREAK.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Обратная связь
Были ли сведения на этой странице полезными?
Обратная связь
Отправить и просмотреть отзыв по
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
VBA циклы — For Next и For Each в Excel
Опубликовано 21.01.2014 Автор Ренат Лотфуллин
Цикл For Loop в VBA – один из самых популярных циклов в Excel. Данный цикл имеет две формы – For Next и For Each In Next. Данные операторы используются для последовательного перемещения по списку элементов или чисел. Для завершения цикла мы можем в любой момент использовать команду выхода. Давайте подробнее рассмотрим каждый из этих циклов.
VBA цикл For Next
Цикл For Next имеет следующий синтаксис:
For счетчик = начало_счетчика To конец_счетчика
‘Какое-то действие
Next счетчик
То что мы делаем здесь, по существу, это создаем цикл, который использует переменную счетчик как хранитель времени. Устанавливаем его значение равным начало_счетчика, и увеличиваем (или уменьшаем) на 1 во время каждого витка. Цикл будет выполняться до тех пор, пока значение счетчик не станет равным конец_счетчика. Когда оба эти значения совпадут, цикл выполнится последний раз и остановится.
Sub пример_цикла1()
For счетчик = 1 to 10
j = счетчик
Next счетчик
msgbox "Значение счетчика на последнем витке равно " & счетчик
End Sub
Последнее значение переменной счетчик будет равным 11
VBA обратный цикл For Loop с инструкцией STEP
Если у вас появилась необходимость перемещаться от большего значения к меньшему – вы можете использовать цикл в обратном направлении. Вот пример обратного цикла:
Sub пример_цикла2()
For счетчик = 10 to 1 Step -1
j = счетчик
Next счетчик
msgbox "Значение счетчика на последнем витке равно " & счетчик
End Sub
Последнее значение переменной счетчик будет равным 1.
Как вы могли заметить, мы можем использовать инструкцию Step n для работы цикла как вперед, так и в обратном направлении. По умолчанию значение Step равно 1, но оно может быть изменено, если необходимо пропускать какие-либо значения, тогда значение n будет больше одного, или перемещаться в обратном направлении, тогда n будет отрицательным.
VBA цикл For Each … Next
Цикл For Each … Next имеет следующий цикл:
For Each элемент_группы In группа_элементов
‘Какое-то действие
Next элемент_группы
Здесь переменная элемент_группы принадлежит к группе_элементов (железная логика. ). Я имею в виду, что объект группа_элементов должен быть коллекцией объектов. Вы не сможете запустить цикл For Each для отдельно объекта (Microsoft сразу оповестит вас об этом 438-й ошибкой).
Данный цикл перебирает все элементы какой-либо коллекции, начиная с самого первого. Вы можете использовать данный цикл, если вам необходимо, например, обойти все листы в книге, объекты на листе, сводные таблицы и т.д.
Ниже представлен пример, как можно воспользоваться циклом For Each для просмотра всех листов книги:
Sub пример_цикла4()
For Each sht In ActiveWorkbook.Worksheets
MsgBox sht.Name
Next sht
End Sub
… либо всех сводных таблиц на листе
Sub пример_цикла()
For Each pvt In ActiveSheet.PivotTables
MsgBox pvt.Name
Next pvt
End Sub
Прерывание цикла VBA
Если вам необходимо выйти из цикла до момента, как будет достигнуто условие завершения цикла, воспользуйтесь командой End For в связке с инструкцией IF. В примере, приведенном ниже, мы выйдем из цикла до момента достижения условия завершения, в данном цикле выход будет осуществлен при условии, когда переменная счетчик будет равна 3.
Sub пример_цикла5()
For счетчик = 0 To 5
MsgBox счетчик
If (счетчик = 3) Then Exit For
Next счетчик
End Sub
Пропуск части цикла в For Each
Пропускать часть цикла, а затем возвращаться назад – плохая практика. Тем не менее, давайте рассмотрим пример:
1
2
3
4
5
6
7
8
9
10
11
12
Sub пример_цикла6 ()
Dim j As Integer
For i = 0 To 5
b:
If (j = 3) Then GoTo a:
j = i
Next i
a:
j = 4
GoTo b:
MsgBox ( "Значение j = " & j)
End Sub
Здесь мы пропустили одну итерацию (когда j = 3). Как вы думаете, какой результат выдаст программа? 3? 5? Ну… на самом деле, ни один из вариантов не верный. Цикл будет выполняться бесконечно, пока память компьютера не переполнится.
Однако возможность пропустить шаг цикла без последствий существует. Вы можете увеличить значение счетчика на 1 (или другое значение), что приведет к пропуску операций, находящихся между этими значениями. Вот пример:
Sub пример_цикла7()
For i = 0 To 5
i = i + 1
MsgBox i
Next i
End Sub
Но опять же, это плохая практика написания кода, и может привести к нежелательным последствиям при написании кода в будущем. Вместо этого, при необходимости пропуска некоторых итераций, попробуйте использовать функцию If или Select Case.
Вам также могут быть интересны следующие статьи
- Расширенный фильтр в Excel с символами подстановки и уникальными значениями на VBA
- Как написать макрос в Excel — создание, запись и редактирование VBA макросов в Excel
- Три способа отправить письмо из Excel с помощю VBA
- Пошаговая инструкция по созданию надстройки Excel для ленты
- Как создать ролловер эффект в Excel — выполнение макроса во время наведения курсора на ячейку в Excel
- Excel дашборд по обслуживанию клиентов — добавление макросов [часть 4 из 4]
Рубрика: Макросы/VBA | Метки: vba, макрос, циклы | 5 комментариев | Permalink
5 комментариев
Здравствуйте! Подскажите можно ли как нибудь сделать, чтобы цикл перемещался по столбцу и искал наименьшее значение, удалял его и снова искал и снова удалял. Остановиться должен лишь тогда, когда сумма значений в этом столбце не будет равна или меньше, какого то базового значения.
>Последнее значение переменной счетчик будет равным 1.
Вообще-то 0 ��
Лажа какая-то, статья как делать не стоит!
НИкогда не пишите Goto.
Доброго дня Вам.
Не сможете ли мне помочь, с циклами …
Я записал макрекордером макрос, который одну строку в таблице «Условно Форматирует».Строка по формуле по значению окрашивается в синий цвет …
но мне нужно чтобы по этой формуле все остальные строчки таблицы так же окрашивались бы по VBA
По-другому вопрос звучит так: как впихнуть макрос который сам записался с помощью
макрорекордером …в цикл чтобы остальные строчки по условному форматированию тоже окрасились
Range(«I13:AO13»).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
«=$A$5>=$I$13:$AO$13»
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 16764006
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range(«O15»).Select
End Sub