This is one of those things that I’m sure there’s a built-in function for (and I may well have been told it in the past), but I’m scratching my head to remember it.
How do I loop through each row of a multi-column range using Excel VBA? All the tutorials I’ve been searching up seem only to mention working through a one-dimensional range…
asked Sep 22, 2009 at 23:53
1
Dim a As Range, b As Range
Set a = Selection
For Each b In a.Rows
MsgBox b.Address
Next
answered Sep 23, 2009 at 0:19
MikeMike
2,9751 gold badge18 silver badges14 bronze badges
0
Something like this:
Dim rng As Range
Dim row As Range
Dim cell As Range
Set rng = Range("A1:C2")
For Each row In rng.Rows
For Each cell in row.Cells
'Do Something
Next cell
Next row
answered Sep 22, 2009 at 23:58
David AndresDavid Andres
31.2k7 gold badges45 silver badges36 bronze badges
1
Just stumbled upon this and thought I would suggest my solution. I typically like to use the built in functionality of assigning a range to an multi-dim array (I guess it’s also the JS Programmer in me).
I frequently write code like this:
Sub arrayBuilder()
myarray = Range("A1:D4")
'unlike most VBA Arrays, this array doesn't need to be declared and will be automatically dimensioned
For i = 1 To UBound(myarray)
For j = 1 To UBound(myarray, 2)
Debug.Print (myarray(i, j))
Next j
Next i
End Sub
Assigning ranges to variables is a very powerful way to manipulate data in VBA.
answered Dec 9, 2015 at 1:33
tc_NYCtc_NYC
1921 gold badge2 silver badges11 bronze badges
1
In Loops, I always prefer to use the Cells class, using the R1C1 reference method, like this:
Cells(rr, col).Formula = ...
This allows me to quickly and easily loop over a Range of cells easily:
Dim r As Long
Dim c As Long
c = GetTargetColumn() ' Or you could just set this manually, like: c = 1
With Sheet1 ' <-- You should always qualify a range with a sheet!
For r = 1 To 10 ' Or 1 To (Ubound(MyListOfStuff) + 1)
' Here we're looping over all the cells in rows 1 to 10, in Column "c"
.Cells(r, c).Value = MyListOfStuff(r)
'---- or ----
'...to easily copy from one place to another (even with an offset of rows and columns)
.Cells(r, c).Value = Sheet2.Cells(r + 3, 17).Value
Next r
End With
answered Aug 20, 2015 at 16:50
LimaNightHawkLimaNightHawk
6,5033 gold badges40 silver badges60 bronze badges
0
Hi, I am trying to create a macro that has a loop which copies a function down column 1 (VOL) and another function down column 2 (CAPACITY) for each Station. This is what I have so far:
Sub TieOut()
Dim i As Integer
Dim j As Integer
For i = 1 To 3
For j = 1 To 3
Worksheets("TieOut").Cells(i, j).Value = "'=INDEX('ZaiNet Data'!$A$1:$H$39038,MATCH('INDEX-MATCH'!Z$7&TEXT('INDEX-MATCH'!$A9,"m/dd/yyyy"),'ZaiNet Data'!$C$1:$C$39038,0), 4)"
Next j
Next i
End Sub
The picture of what I WANT is below: You can see that I have manually copied and pasted my two functions down each column. I just need a macro that can loop through it.
The function I want to be looped down the VOL column for each Station is:
=INDEX('ZaiNet Data'!$A$1:$H$39038,MATCH('INDEX-MATCH'!Z$7&TEXT('INDEX-MATCH'!$A438,"M/DD/YYYY"),'ZaiNet Data'!$C$1:$C$39038,0), 4)
The function I want to be looped down the CAPACITY column for each Station is:
=INDEX('ZaiNet Data'!$A$1:$H$39038,MATCH('INDEX-MATCH'!Z$7&TEXT('INDEX-MATCH'!$A438,"M/DD/YYYY"),'ZaiNet Data'!$C$1:$C$39038,0), 5)
Could someone please help? Thank you!
UPDATE
****How can I make the loop run automatically without having to manually enter the formula into the first two cells and click on macro?
Also how can I make the loop run through all the columns/rows? (horizontically)****
I included two screen shots to show what I mean. Below is my current code.
Thanks!
Sub Loop3()
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 1).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Select
Dim i As Integer
Dim j As Integer
With Worksheets("Loop")
i = 1
Do Until .Cells(10, i).Value = "blank"
For j = 1 To 10
.Cells(j, i).Formula = "=INDEX('ZAINET DATA'!$A$1:$H$39038,MATCH(Loop!E$7&TEXT(Loop!$A9,""M/D/YYYY""),'ZAINET DATA'!$C$1:$C$39038,0),4)"
.Cells(j, i + 1).Formula = "=INDEX('ZAINET DATA'!$A$1:$H$39038,MATCH(Loop!E$7&TEXT(Loop!$A9,""M/D/YYYY""),'ZAINET DATA'!$C$1:$C$39038,0),5)"
Next j
i = i + 2
Loop
End With
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 1).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Select
End Sub
Содержание
- Использование операторов For Each. Next
- Проход по диапазону ячеек
- Выход из цикла For Each. Next до его завершения
- Использование цикла For Each. Next для итерации в классе VBA
- См. также
- Поддержка и обратная связь
- Проход по диапазону ячеек
- Поддержка и обратная связь
- VBA For Each примеры
- Сила циклов VBA
- Как работает цикл For Next?
- Два типа циклов For Next
- The For Each Next Loop: цикл по коллекции элементов
- В каком порядке работает цикл For Each?
- Предметы / объекты НЕ выбираются в цикле
- The Next Loop: циклы через набор чисел
- Обратный цикл
- Как остановить цикл раньше
- Переменная не требуется после ключевого слова Next
- Примеры макросов VBA для циклов
- Какую задачу хотите зациклить вы?
Использование операторов For Each. Next
Операторы For Each. Next повторяют блок операторов для каждого объекта в коллекции или каждого элемента массива. Visual Basic автоматически присваивает переменную при каждом выполнении цикла. Например, следующая процедура добавляет 10 к значению каждой ячейки в диапазоне от A1 до A10.
Указанный ниже код перебирает все элементы массива и устанавливает для каждого из них значение переменной-счетчика I.
Проход по диапазону ячеек
Цикл For Each. Next можно использовать для прохода по диапазону ячеек. Приведенная ниже процедура проходит по диапазону ячеек A1:D10 на листе Sheet1 и присваивает каждой ячейке, абсолютное значение которой меньше 0,01, значение 0 (ноль).
Выход из цикла For Each. Next до его завершения
Из цикла For Each. Next можно выйти с помощью оператора Exit For. Например, при возникновении ошибки можно использовать оператор Exit For в блоке True оператора If. Then. Else или оператора Select Case, который используется непосредственно для проверки ошибок. Если ошибка не возникает, оператор If…Then…Else получает значение False, и цикл продолжает работу.
В следующем примере выполняется поиск первой ячейки диапазона A1:B5, не содержащей число. Если такая ячейка найдена, выводится сообщение и выполняется выход из цикла с помощью оператора Exit For.
Использование цикла For Each. Next для итерации в классе VBA
Для каждого. Следующие циклы не только итерируют массивы и экземпляры объекта Collection . Они также могут выполнять итерацию в созданном вами классе VBA.
Ниже приведен пример, в котором показано, как это сделать.
Создайте модуль класса в редакторе Visual Basic (VBE) и присвойте ему имя CustomCollection. cc1
Вставьте следующий код в только что созданный модуль.
Экспортируйте этот модуль в файл и сохраните его в локальном расположении. cc2
После экспорта модуля откройте экспортированный файл, используя текстовый редактор (программы Блокнот в Windows должно быть достаточно). Содержимое файла должно выглядеть следующим образом.
Используя текстовый редактор, удалите знак ‘ из первой строки под текстом Property Get NewEnum() As IUnknown в файле. Сохраните измененный файл.
В редакторе VBE удалите класс, созданный из проекта VBA, и не экспортируйте его при появлении соответствующего запроса. cc3
Импортируйте файл, из которого вы удалили знак ‘ , назад в VBE. cc4
Выполните следующий код, чтобы проверить возможность итерации в своем созданном пользовательском классе VBA, используя VBE и текстовый редактор.
| Сноски | Описание |
|---|---|
| [cc1] | Вы можете создать модуль класса, выбрав Class Module (Модуль класса) в меню Insert (Вставка). Вы можете переименовать модуль класса, изменив его свойства в окне Properties (Свойства). |
| [cc2] | Вы можете активировать диалоговое окно Экспорт файла, выбрав Export File (Экспорт файла) в меню File (Файл). |
| [cc3] | Вы можете удалить модуль класса из VBE, выбрав Remove Item (Удалить элемент) в меню File (Файл). |
| [cc4] | Вы можете импортировать файл внешнего модуля класса, запустив диалоговое окно Импорт файла (выберите Import File (Импорт файла) в меню File (Файл)). |
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Источник
Проход по диапазону ячеек
При использовании Visual Basic часто требуется выполнить один и тот же блок операторов в каждой ячейке диапазона. Для этого необходимо объединить оператор цикла и один или несколько методов для идентификации каждой ячейки по отдельности и выполнить операцию.
Один из способов пройти по диапазону — использовать цикл For. Next со свойством Cells. С помощью свойства Cells можно заменить номера индексов ячеек счетчиком циклов (или другими переменными или выражениями). В следующем примере индекс строки заменяется переменной counter . Процедура проходит по диапазону ячеек C1:C20, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01.
Еще один простой способ пройти по диапазону — использовать цикл For Each. Next с коллекцией ячеек, указанной в свойстве Range. Visual Basic автоматически присваивает объектную переменную для следующей ячейки при каждом выполнении цикла. Следующая процедура проходит по диапазону ячеек A1:D10, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01.
Если вы не знаете границы диапазона, по которому нужно пройти, можно использовать свойство CurrentRegion, чтобы возвратить диапазон, окружающий активную ячейку. Например, при запуске на листе следующая процедура выполняет проход по диапазону, окружающему активную ячейку, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Источник
VBA For Each примеры
Итог: циклы For Next — одни из самых мощных методов VBA для автоматизации общих задач в Excel. В этой статье объясняется, как работает цикл, повторяющий действия над коллекцией элементов, что экономит нам массу времени.
Уровень мастерства: Средний
Сила циклов VBA
В Excel мы тратим много времени на повторяющиеся простые задачи. Например, как: форматирование нескольких диапазонов, отображение нескольких листов, копирование и вставка в несколько рабочих книг, применение фильтров к нескольким таблицам или сводным таблицам, замена значений, обновление формул и т.д.
Можете ли вы вспомнить несколько задач, в которых вам приходилось повторять один и тот же процесс снова и снова?
Эти задачи чрезвычайно трудоемкие и скучные!
К счастью, выход есть. Мы можем использовать циклы в наших макросах VBA, чтобы очень быстро повторять действия. Задачи, выполнение которых вручную может занять несколько часов, могут быть выполнены за несколько секунд с помощью цикла.
Цикл For Next — это наиболее распространенный тип цикла, который помогает нам выполнять эти повторяющиеся задания. В этой статье мы рассмотрим два типа For Next Loops.
Загрузите файл примера
Загрузите бесплатный файл Excel, содержащий примеры макросов с помощью цикла For Next.
For Next Loop VBA Macro Examples.xlsm (79.0 KB)
Скачать PDF версию статьи на английском для печати.
Как работает цикл For Next?
Цикл For Next позволяет нам просматривать коллекцию, элементами которой могут быть объекты или списки чисел.
- Клетки в ассортименте.
- Рабочие листы в рабочей тетради.
- Открытые рабочие тетради на компьютере.
- Сводные таблицы на листе.
- Сводные поля в сводной таблице.
- Формы на листе.
- И любой другой объект, с которым вы взаимодействуете в Excel.
Задача цикла For Next Loop состоит в том, чтобы выполнять одинаковые действия (строки кода) для каждого элемента в коллекции.
В приведенном ниже примере содержится цикл For Next, который проходит по каждому листу в книге и отображает каждый лист. Цикл начинается с первого элемента в коллекции (первый лист в рабочей книге) и выполняет строку кода между строками For и Next для каждого элемента в коллекции (каждый лист в рабочей книге).
Конечно, мы можем использовать логические операторы, такие как операторы If, для проверки свойств и условий перед выполнением действий.
Следующий макрос отображает только те листы, которые имеют фразу «ABC Global Co.» в ячейке A1 каждого листа, и скрывает все остальные листы.
Два типа циклов For Next
Действительно, существуют два типа For Next Loops.
- For Each Next Loops циклически просматривает коллекцию элементов.
- For Next Loops цикл через набор чисел.
Давайте посмотрим, как работает каждый.
The For Each Next Loop: цикл по коллекции элементов
Как мы видели выше, цикл «The For Each Next Loop» позволяет нам перебирать коллекцию предметов или объектов. Это, наверное, самый распространенный цикл, который мы используем в Excel, потому что мы работаем с коллекциями объектов. Опять же, эти коллекции представляют собой ячейки в диапазоне, рабочие таблицы в рабочей книге, сводные таблицы в рабочей таблице и т.д.
Мы будем использовать пример написания цикла «For Each Next Loop», чтобы просмотреть все рабочие листы в рабочей книге.
Существует четыре основных шага для написания цикла For Each Next в VBA:
- Объявите переменную для объекта.
- Напишите для каждой строки переменную и коллекцию ссылок.
- Добавьте строку (и) кода для повтора для каждого элемента в коллекции.
- Напишите следующую строку, чтобы закрыть цикл.
Давайте рассмотрим каждый из этих шагов подробно.
Шаг 1 — объявить переменную для объекта
Сначала нам нужно объявить переменную, которая будет временно хранить ссылку на объект.
Строка Dim в верхней части макроса объявляет переменную, как объект. В этом случае объект является рабочим листом. Мы можем создать любое имя переменной, если захотим, если оно не совпадает с другой ссылкой в VBA. «Ws» — наиболее распространенное имя переменной для объекта листа, но вы можете изменить его.
Шаг 2 — Для каждой строки
Далее мы напишем оператор For Each. Это первая строка кода в цикле.
Первые два слова For Each. Затем мы вводим имя переменной, за которым следует слово In. Наконец, мы указываем, где располагается коллекция. В этом случае мы хотим просмотреть все рабочие листы в ActiveWorkbook. Итак, мы набираем ActiveWorkbook.Worksheets. Эта строка ссылается на все рабочие листы в ActiveWorkbook.
Если вы хотите пройтись по рабочим листам определенной рабочей книги, вы можете использовать свойство Рабочие книги для ссылки на эту рабочую книгу по имени.
Просто помните, что рабочая книга, на которую вы ссылаетесь, должна быть открыта до запуска строки кода For Next. Конечно, мы можем использовать метод Workbooks.Open, чтобы открыть рабочую книгу.
Шаг 3 — Добавить код для повторения для каждой итерации
После строки «For Each» мы добавляем строку(и) кода, которая будет выполняться на каждом листе. В этом примере у нас есть только одна строка кода, которая показывает лист.
В этой строке кода мы используем переменную ws для ссылки на текущий рабочий лист в цикле. Когда цикл выполняется, он устанавливает временную ссылку на переменную ws для каждой итерации в цикле.
Это так же, как если бы мы установили переменную ws для определенного листа, используя следующую строку кода.
Однако нам НЕ нужна эта строка с циклом For Each Next. Цикл заботится о настройке переменной для нас для каждой итерации в цикле.
Для первой итерации в цикле ws установлен на Worksheets (1). На следующей итерации ws устанавливается в Worksheets (2). Это продолжается, пока цикл перебирает все листы в рабочей книге. Это очень мощно, потому что мы можем повторно использовать переменную для ссылки на лист в цикле несколько раз.
Шаг 4 — Next закрывает цикл
Последняя строка кода в цикле — Next.
Когда макрос попадает в эту строку кода, он делает две вещи:
- Во-первых, он изменяет ссылку на переменную на следующий элемент коллекции. В этом примере переменная ws изменяется для ссылки на следующий лист в рабочей книге.
- Во-вторых, он возвращается к выполнению строки кода непосредственно под строкой For Each. Затем он выполняет все строки кода между строками For Each и Next в порядке сверху вниз.
Когда будет достигнут последний элемент в коллекции (рабочий лист в рабочей книге), цикл останавливается, и макрос переходит к следующей строке кода ниже строки Next.
В каком порядке работает цикл For Each?
Цикл For Each Loop всегда начинается с первого элемента в коллекции и переходит к последнему элементу в том порядке, в котором они отображаются в Excel. Это основано на номере индекса предметов в коллекции. Вот несколько примеров порядка выполнения цикла для общих объектов.
- Рабочие листы. Начинается с первой вкладки листа в рабочей книге и возвращается к последней в том порядке, в котором вкладки отображаются в рабочей книге.
- Рабочие книги. Начинается с первой открытой книги и циклически повторяется в порядке открытия рабочих книг. Рабочим книгам присваивается порядковый номер по мере их открытия.
- Клетки: циклы слева направо, затем вниз. Начинается с первой ячейки в диапазоне и возвращается к следующему столбцу в той же строке, затем переходит к следующей строке.
- Таблицы и сводные таблицы. Начинается с первого объекта, созданного на листе, и циклически повторяется в порядке создания объектов. Этот же принцип действует и для других объектов, которые вы создаете на листах, таких как фигуры, диаграммы, слайсеры и т.д.
Предметы / объекты НЕ выбираются в цикле
Важно отметить, что когда мы перебираем коллекцию объектов, каждый объект НЕ выделяется в Excel.
Цикл создает ссылку на элемент/объект с помощью переменной. Переменная временно установлена на ссылку на объект. Объект НЕ выбран и НЕ обязательно становится активным объектом. Для выбора объекта мы можем использовать методы Select или Activate. Вы также должны убедиться, что объекты родительского объекта выбраны первыми. Ознакомьтесь с моей статьей об объектной модели Excel и иерархии объектов в VBA, чтобы узнать больше об этом.
The Next Loop: циклы через набор чисел
Также можно использовать цикл For Next Loop для просмотра набора чисел. Это может быть полезно, когда мы перебираем элементы управления пользовательской формы, массивы или если мы хотим перебрать коллекцию в обратном направлении.
Основная операция цикла For Next такая же, как и для цикла For Each. Разница заключается в формате строки For.
Шаг 1 — Объявление переменной для числа
Чтобы перебрать набор чисел, мы сначала должны объявить переменную для целого числа типа данных. Мы можем использовать Integer или Long integer.
Переменная называется счетчиком, потому что она увеличивается или уменьшается вверх/вниз для каждой итерации в цикле.
Дополнительное примечание к Long: тип данных Long (целое число) содержит большее число, чем Integer. Это занимает больше памяти, но для современного компьютера это не проблема. Мы можем использовать длинные переменные все время. Буква L выглядит как цифра 1 в VBA, поэтому я теперь использую i в качестве имени переменной, хотя я использую Long в качестве типа данных. Это все зависит от личных предпочтений, и вы можете назвать переменную как хотите.
Шаг 2 — Напишите строку For
Далее мы пишем строку For. Базовая конструкция — это ключевое слово For, за которым следует имя переменной (counter), затем знак равенства, начальное значение To end value.
Начальные и конечные значения могут быть указаны как числа, или мы можем использовать целочисленные / длинные переменные вместо них.
Эта строка кода возвращает количество листов в активной книге. Тем не менее, он не проходит по каждому листу. Цикл просто перебирает набор чисел. Мы должны создать ссылку на лист с переменной счетчика (i) в качестве номера индекса свойства Worksheets. Шаг 3 показывает эту ссылку.
Шаг 3 — Добавить код, который повторяется для каждой итерации
Остальная часть цикла работает так же, как цикл For Each. Мы можем добавить строки между строками For и Next, которые будут выполняться для каждой итерации цикла. Переменная counter может быть использована несколько раз в этих строках кода.
Шаг 4 — Строка Next закрывает цикл
Наконец, мы добавляем строку Next внизу.
Когда макрос запускается, он устанавливает переменную, равную первому числу в строке For. Когда макрос попадает в следующую строку, он добавляет 1 к значению переменной или увеличивает счет. Итак, я = 2 во второй итерации цикла. Он продолжает цикл, пока не будет достигнут последний номер в цикле.
По умолчанию 1 добавляется к счетчику переменных для каждой итерации в цикле. Это называется значением шага, и мы можем контролировать значение каждого шага в счетчике. Значение Step добавляется в конец строки For. Следующая строка добавит 2 к счетчику для каждой итерации в цикле.
Если вы хотите заштриховать все остальные строки на листе, вы можете использовать такой цикл.
Обратный цикл
Мы также можем использовать значение шага для обратной петли, указав отрицательное число.
Обратите внимание, что начальное значение теперь больше, а конечное значение меньше. Циклы начинаются с 100 (начальное значение) и вычитают 1 из переменной счетчика (шаг -1) для каждой итерации в цикле, пока не дойдут до 1 (конечное значение).
Ключевое слово Step не является обязательным. Если вы не укажете его, тогда VBA примет значение шага 1.
Цикл в обратном направлении — это хорошо, если вы удаляете элементы.
Я напишу отдельный пост об этом, но общая идея заключается в том, что когда мы перебираем коллекцию и удаляем элементы, размер коллекции уменьшается при удалении элементов. Цикл обычно выдает ошибку, как только достигает 10-го элемента, когда в коллекции теперь только 9 элементов. Цикл в обратном направлении предотвращает эту потенциальную ошибку.
Как остановить цикл раньше
Обычно цикл перебирает все элементы в коллекции, а затем переходит к следующей строке кода ниже следующей строки. Однако мы можем остановить цикл раньше с помощью оператора Exit For.
В следующем макросе оператор Exit For используется для выхода из цикла после того, как первый лист, начинающийся со слова «Отчет», будет найден скрытым.
Переменная ws сохраняет ссылку на рабочий лист после преждевременного выхода из цикла и может быть снова использована в коде под циклом.
Переменная не требуется после ключевого слова Next
Возможно, вы заметили, что я добавил переменную после ключевого слова Next в нижней части цикла в приведенных выше примерах.
Это НЕ обязательно, и вы можете не увидеть его в других примерах, которые вы найдете в Интернете. Однако мне нравится включать переменную после Next по двум причинам.
- Мы можем использовать его при отладке кода, чтобы увидеть значение переменной, наведя указатель мыши на переменную, когда код остановлен.
- Это облегчает понимание того, к какой строке For подключена следующая строка. Это особенно верно, когда у вас есть несколько циклов или вложенных циклов в ваших макросах.
Поэтому я рекомендую добавить переменную после ключевого слова Next в качестве лучшей практики. Немного дополнительной работы заранее сэкономит время и головную боль в будущем. Доверьтесь мне!
Примеры макросов VBA для циклов
Вот дополнительные статьи с макросами, в которых используется хотя бы один цикл For Next Loop.
Макрос метрики SUBTOTAL — создание сводной таблицы всех типов функций
Как добавить оглавление Галерея изображений в файлы Excel
Скрыть и отобразить (отфильтровать) столбцы с помощью слайсера или раскрывающегося меню фильтра
Как изменить размер панелей Excel для разных размеров экрана
Макрос VBA, чтобы скрыть все столбцы, которые содержат значение в ячейке
Как повторять задачи с помощью кода VBA — Looping — Отличная статья с большим количеством примеров от моего друга Криса Ньюмана из The Spreadsheet Guru.
Какую задачу хотите зациклить вы?
Я надеюсь, что эта статья поможет вам начать работу с циклами. Не забудьте скачать бесплатный файл Excel, содержащий примеры кода.
For Next Loop VBA Macro Examples.xlsm (79.0 KB)
Циклы, безусловно, являются промежуточной техникой кодирования, которая вынуждает нас выйти за рамки MacroRecorder. К сожалению, записанный макрос не может создавать петли. Тем не менее, это навык, который вы сможете использовать снова и снова на протяжении всей своей карьеры для автоматизации простых и сложных задач. Понимание того, как использовать циклы, даст вам волшебную силу с Excel.
Пожалуйста, оставьте комментарий ниже с задачей, которую вы хотите автоматизировать с помощью цикла. Спасибо!
Источник
Цикл For Each… Next в VBA Excel, его синтаксис и описание отдельных компонентов. Примеры использования цикла For Each… Next.
Цикл For Each… Next в VBA Excel предназначен для выполнения блока операторов по отношению к каждому элементу из группы элементов (диапазон, массив, коллекция). Этот замечательный цикл применяется, когда неизвестно количество элементов в группе и их индексация, в противном случае, более предпочтительным считается использование цикла For…Next.
|
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 выделите диапазон ячеек и запустите на выполнение следующую процедуру:
|
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:
|
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.
|
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.
|
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.
|
Добрый день. Нужна помощь в создании макроса, а точнее цикла. Я в этом деле новенький и пока есть проблемы и пробелы в знания. Есть скрипт для работы в SAP. Его необходимо вставить в макрос и что б макрос по очереди брал данные из строк с листа EXCEL. Я это вижу так. В скрипте есть переменные типа n1.n2.n3.n4….. Все они находятся в первой строке таблицы. Макрос берет скрипт с переменными в первой строке, выполняет все действия из скрипта и переходит к второй строке (переменные стоят в тех же столбцах но на одну строку ниже) берет их, опять вставляет в скрипт и выполняет его. И так весь цикл пока данные не кончатся. Количество строк может меняться. От 200 и до 1000. За любую помощь буду очень благодарен. |
|
|
Roman M  Пользователь Сообщений: 58 |
Добрый день. А почему не использовать этот скрипт в макросе ? Почему именно в скрипт вставлять переменные ? |
|
Roman M
, Наверное я не очень корректно описал проблему. Скрипт должен быть в макросе и при запуске макроса скрипт должен по очереди брать данные из таблицы. Скрипт взял данные из первой строки сделал все действия потом данные из второй строки и так по кругу. |
|
|
Roman M Пользователь Сообщений: 58 |
Т.е. вам нужен пример скрипта в Excel, который что-то делал бы в SAP? |
|
Mershik  Пользователь Сообщений: 8277 |
Evgenii Kov, спрошу так ? ЗАЧЕМ ЗДЕСЬ УПОМИНАНИЕ SAP? Вам нужно работа в файле EXCEL? ЦИКЛЫ ТУТ если какой-то конкретный вопрос — то ознакомитесь с правилами там есть что почитать (про файл-пример например) Изменено: Mershik — 02.10.2020 15:31:26 Не бойтесь совершенства. Вам его не достичь. |
|
Roman M, нет. С САПом у меня все нормально. Нужен макрос который будет построчно брать данные с листа экселя (с каждым новым циклом смещаться на одну строку вниз) и подставлять их в скрипт в места где стоят переменные типа n1. n2. n3. |
|
|
Mershik, SAP вписал машинально. спасибо за ссылку. Буду пробовать искать подходящий. Файл пример скинуть нет возможности. Он на рабочем компе. Там все заблокировано. Изменено: Evgenii Kov — 02.10.2020 15:33:41 |
|
|
Mershik Пользователь Сообщений: 8277 |
#8 02.10.2020 15:38:33 плохо прочитали там файл-пример, а не Ваш рабочий документ…
Не бойтесь совершенства. Вам его не достичь. |
||
|
Roman M Пользователь Сообщений: 58 |
У вас есть скрипт (.vbs ?), который что-то делает и вам в него нужно передать переменные из Excel? |
|
Да. и что переменные после каждого цикла брались со следующей строки. |
|
|
Roman M Пользователь Сообщений: 58 |
#11 02.10.2020 16:10:18 Будет примерно так В Excel макрос
В скрипте добавляете в начале
и дальше в скрипте подставляете эти переменные. К модераторам: тему можно поменять на «Передать циклом переменные в скрипт/vbs» |
||||
|
Roman M
спасибо. буду пробовать. |
|
|
Jack Famous  Пользователь Сообщений: 10848 OS: Win 8.1 Корп. x64 | Excel 2016 x64: | Browser: Chrome |
#13 02.10.2020 16:55:27
этот
я бы не был так уверен. Поэтому Roman M и обратился к модераторам По вопросу: уверены, что хорошая идея передавать из приложения переменные в скриптовый файл? Почему не сделать всё из Excel? Будет и проще, и быстрее, и надёжнее Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄ |
||||
|
Evgenii Kov  Пользователь Сообщений: 56 |
#14 02.10.2020 17:15:01 Jack Famous, у меня все в Excel. есть макрос в который вставлен скрипт. Макрос заходит в САП, идет по шагам скрипта, в нужных шагах вставляет данные с листа Excel (данные расположены в виде строк). Когда скрипт заканчивается он должен вернутся в начало и опять идти по темже шагам скрипта только брать данные из стоки ниже. И так по кругу. Некоторые шаги типа session.findById(«wnd[0]») я заменю на переменные.
Изменено: Evgenii Kov — 05.10.2020 08:03:51 |
||
|
Юрий М  Модератор Сообщений: 60581 Контакты см. в профиле |
Evgenii Kov, код следует оформлять соответствующим тегом: ищите такую кнопку (см. скрин) и исправьте своё сообщение. |
|
Evgenii Kov Пользователь Сообщений: 56 |
#16 05.10.2020 19:03:14 Roman M, Пожалуйста, подскажите где ошибка ))
|
||
|
Evgenii Kov Пользователь Сообщений: 56 |
#17 05.10.2020 19:06:45 Jack Famous, я попробовал добавить все в макрос. и вот что получилось. работать не хочет. уже не знаю что делать. |