Excel цикл

Проход по диапазону ячеек Looping Through a Range of Cells

При использовании Visual Basic часто требуется выполнить один и тот же блок операторов в каждой ячейке диапазона. When using Visual Basic, you often need to run the same block of statements on each cell in a range of cells. Для этого необходимо объединить оператор цикла и один или несколько методов для идентификации каждой ячейки по отдельности и выполнить операцию. To do this, you combine a looping statement and one or more methods to identify each cell, one at a time, and run the operation.

Один из способов пройти по диапазону — использовать цикл For. Next со свойством Cells. One way to loop through a range is to use the For. Next loop with the Cells property. С помощью свойства Cells можно заменить номера индексов ячеек счетчиком циклов (или другими переменными или выражениями). Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. В следующем примере индекс строки заменяется переменной counter . In the following example, the variable counter is substituted for the row index. Процедура проходит по диапазону ячеек C1:C20, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01. The procedure loops through the range C1:C20, setting to 0 (zero) any number whose absolute value is less than 0.01.

Еще один простой способ пройти по диапазону — использовать цикл For Each. Next с коллекцией ячеек, указанной в свойстве Range. Another easy way to loop through a range is to use a For Each. Next loop with the collection of cells specified in the Range property. Visual Basic автоматически присваивает объектную переменную для следующей ячейки при каждом выполнении цикла. Visual Basic automatically sets an object variable for the next cell each time the loop runs. Следующая процедура проходит по диапазону ячеек A1:D10, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01. The following procedure loops through the range A1:D10, setting to 0 (zero) any number whose absolute value is less than 0.01.

Если вы не знаете границы диапазона, по которому нужно пройти, можно использовать свойство CurrentRegion, чтобы возвратить диапазон, окружающий активную ячейку. If you do not know the boundaries of the range you want to loop through, you can use the CurrentRegion property to return the range that surrounds the active cell. Например, при запуске на листе следующая процедура выполняет проход по диапазону, окружающему активную ячейку, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01. For example, the following procedure, when run from a worksheet, loops through the range that surrounds the active cell, setting to 0 (zero) any number whose absolute value is less than 0.01.

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник: docs.microsoft.com

Канал в Telegram

Вы здесь

Работа с циклом For в VBA

В этом уроке будет рассмотрена работа с циклом For в VBA. Пример работы с циклом For, так же будет продемонстрирован пример создания формул в Excel с помощью макросов.

Цикл For работает по принципу счетчика. For применяется в тех случаях, когда необходимо повторить некоторые действия заранее известное кол-во раз. Например, цикл For часто используется при чтении массивов.

Цикл For имеет следующий синтаксис:
For счетчик = начало цикла To конец цикла [Step шаг]группа операторов, команд и т.д.
Exit For
Next счетчик

  • “счетчик” – переменная, которая изменяется на указанный “шаг”. Если шаг не указан, то по умолчанию берется единица.
  • “начало цикла”, “конец цикла” – числа или переменные указывающие нижний предел счетчика и верхний. Остановка цикла происходит тогда, когда “счетчик” >конец цикла” (или, если цикл обратный, т.е. с шагом -1, то “счетчик” n;
  • Cells(i, 4) – ячейка выделенного листа, i номер строки, 4 -номер столбца в который выводится результат. Обратите внимание, наш счетчик i указывает номер строки листа Excel;
  • Next i – оператор закрытия цикла и перевода указателя к For. Все что находится между For и Next выполняется в цикле;
  • CStr – функция преобразующая число в текст.

Ячейке мы присваиваем формулу созданную следующим образом “=C” & CStr(i) & “+E” & CStr((n – i) + 2). Знак & – “склеивание” символов, строк. В результате у нас получится формула “=Сn+E((n – i) + 2)” где n = 21, i – счетчик.
Страшно? Это только кажется 🙂

Все. После выполнения макроса мы получим следующий столбец (выделен), а в каждой ячейке формула:

Пример 2
Теперь рассмотрим цикл с указанным шагом. После расчета прошлого макроса мы получили три столбца, теперь нам необходимо из столбца E вычесть D, в столбец F вывести формулы вычитания. Код макроса следующий:

Sub Цикл_For_с_шагом()
Const n = 21
For i = n To 2 Step -1
Cells(i, 6) = “=E” & CStr(i) & “-D” & CStr(i)
Next i
End Sub

В данном случае все тоже самое, только цикл теперь “бежит” не от 2, а от 21 до 2 с шагом (Step) -1.
Результат выполнения получим следующий:

Цикл For, в VBA, является не единственным циклом. В дальнейшем будут рассмотрены еще пара вариантов циклов, без которых не обойтись при написании макрокоманд в Excel.

Источник: www.programm-school.ru

Циклы в VBA

Встречаются ситуации, когда от программы VBA требуется совершить несколько раз подряд один и тот же набор действий (то есть повторить несколько раз один и тот же блок кода). Это может быть сделано при помощи циклов VBA.

К циклам VBA относятся:

Далее мы подробно рассмотрим каждый из этих циклов.

Оператор цикла «For» в Visual Basic

Структура оператора цикла For в Visual Basic может быть организована в одной из двух форм: как цикл For … Next или как цикл For Each.

Цикл «For … Next»

Цикл For … Next использует переменную, которая последовательно принимает значения из заданного диапазона. С каждой сменой значения переменной выполняются действия, заключённые в теле цикла. Это легко понять из простого примера:

В этом простом цикле For … Next используется переменная i, которая последовательно принимает значения 1, 2, 3, … 10, и для каждого из этих значений выполняется код VBA, находящийся внутри цикла. Таким образом, данный цикл суммирует элементы массива iArray в переменной Total.

В приведённом выше примере шаг приращения цикла не указан, поэтому для пошагового увеличения переменной i от 1 до 10 по умолчанию используется приращение 1. Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step, как показано в следующем простом примере.

Так как в приведённом выше примере задан шаг приращения равный 0.1, то переменная dTotal для каждого повторения цикла принимает значения 0.0, 0.1, 0.2, 0.3, … 9.9, 10.0.

Для определения шага цикла в VBA можно использовать отрицательную величину, например, вот так:

Здесь шаг приращения равен -1, поэтому переменная i с каждым повторением цикла принимает значения 10, 9, 8, … 1.

Цикл «For Each»

Цикл For Each похож на цикл For … Next, но вместо того, чтобы перебирать последовательность значений для переменной-счётчика, цикл For Each выполняет набор действий для каждого объекта из указанной группы объектов. В следующем примере при помощи цикла For Each выполняется перечисление всех листов в текущей рабочей книге Excel:

Оператор прерывания цикла «Exit For»

Оператор Exit For применяется для прерывания цикла. Как только в коде встречается этот оператор, программа завершает выполнение цикла и переходит к выполнению операторов, находящихся в коде сразу после данного цикла. Это можно использовать, например, для поиска определённого значения в массиве. Для этого при помощи цикла просматривается каждый элемент массива. Как только искомый элемент найден, просматривать остальные нет необходимости – цикл прерывается.

Применение оператора Exit For продемонстрировано в следующем примере. Здесь цикл перебирает 100 записей массива и сравнивает каждую со значением переменной dVal. Если совпадение найдено, то цикл прерывается:

Цикл «Do While» в Visual Basic

Цикл Do While выполняет блок кода до тех пор, пока выполняется заданное условие. Далее приведён пример процедуры Sub, в которой при помощи цикла Do While выводятся последовательно числа Фибоначчи не превышающие 1000:

В приведённом примере условие iFib_Next

Источник: office-guru.ru

Циклы VBA

Доброго времени суток! Данную статью я решил посвятить рубрике по основам программирования в Visual Basic for Application. И сегодня мы поговорим о циклах в VBA, разберём их синтаксис и рассмотрим несколько примеров, которые часто встречаются программисту.

Сначала напомню, что цикл — процесс повторения части кода, выполняемый, пока не будет выполнено или нарушено заданное нами условие.

В данной статье мы разберём синтаксис и примеры следующих циклов в VBA:

  • For
  • For each
  • While
  • Until

Цикл For в VBA


Цикл for в VBA обычно используется при зацикливании фрагмента кода, если нам известно конечное значение counter — счетчика, при котором мы выйдем из цикла.
Возьмём для примера самый распространённый пример:

Сгенерировать массив из 5 целых значений

Обратите ваше внимание, что в этом примере используется неявное объявление при работе с циклами в VBA. i% — означает неявное объявление переменной i в формате integer. Такая конструкция по сути заменяет следующую: dim i as integer . Это используется для сокращения кода и для удобства написания и чтения. В старых версиях VBA необходимо указывать знак формата после каждого использования неявной переменной. В более поздних версиях достаточно всего один раз.

VBA для цикла for даёт возможность использовать функцию Step. Как ясно из перевода, это шаг, с которым мы будем проходить наш интервал. По умолчанию, он равен 1. Популярный вариант использования встречается в случаях, когда counter связан с переменной, используемой внутри цикла. Например, при написании программ, связанных с функциями.

Найти пересечение графика функции y = 5*x + 5 с осью ординат

Теперь представим, что у нас достаточно большой диапазон и мы не хотим заставлять компьютер считать лишние итерации. На этот случай существует команда Exit For. Перепишем наш последний пример с новой командой.

C помощью команды Exit можно закончить выполнение любого цикла в VBA. Достаточно указать после Exit название используемого цикла. Также им возможно завершить работу любой процедуры или функции.

Цикл For Each в VBA


For Each в VBA основан на переборе всех элементов, указанного типа в массиве, объекте или группе.
Самый популярный вариант его использования — перебор страниц в рабочей книге.

Вывести названия всех листов в рабочей книге

И ещё один интересный пример:

Изменить размер шрифта и выравнить по центру текст в label

Тут следует понимать, что через Controls можно обратиться к любому элементу формы. Если отфильтровать по имени, например, как мы сделали выше, то можно выделить группы элементов и изменить их свойства. В данном случае, размер шрифта и выравнивание.

Цикл While в VBA


Циклы в VBA, которые используют структуру Do..Loop (это while и until циклы) можно записывать с разным расположением фрагмента условия. Как видите на картинке выше, условие может проверяться после выполнения одной итерации, а может перед запуском цикла.
Самый популярный пример:

Отсортируйте по возрастанию сгенерированный массив методом пузырька

В вышеуказанном примере мы отсортировали массив с рандомными значениями в порядке возрастания. Метод пузырька считается достаточно долгим, но простым в реализации. В основном, им сортируют небольшие числовые массивы.

Цикл Until в VBA


Как видите, отличия от while крайне несущественные. Цикл Until в VBA можно реализовать с помощью конструкции while NOT (condition). Тем не менее, приведу пример:

Заставить пользователя ввести число

Почему заставить? Потому, что если пользователь закроет окно ввода, это его не спасёт, оно будет появляться вновь и вновь пока он не введёт любое число.

На этом мы закончим. Сегодня мы рассмотрели важную тему циклов в VBA, разобрали основные примеры. Конечно все примеры по этой обширной теме сложно разобрать, но, тем не менее, основы вы должны понять. Оставляйте ваши комментарии, если у вас возникли вопросы.
Скачать исходник

Источник: codetown.ru

Excel цикл

Вы уже знаете основные объекты VBA Excel и действия с ними. Пора переходить к настоящей автоматизации.

Циклы

Циклы – повторяющиеся действия. Бывает нужно сделать одно и то же действие определенное количество раз (например, на пяти листах вставить столбец с одинаковыми данными или заголовком), а бывает, что нужно делать какое-то действие “до тех пор, пока . ” (например, пока не найдется строка с нужными данными). В VBA для двух этих случаев есть два цикла: “for” – для повторения действий заданное число раз, и “while” – для повторения действий “до тех пор, пока . “. Разница между двумя циклами только одна: в первом случае вы знаете, сколько раз надо повторить цикл, а во втором – не знаете, но знаете, когда нужно остановиться. Есть и другие циклы, но их можно легко заменить этими двумя.

Цикл for

Возьмем наш пример: нужно на пять листов вставить, например, первый столбец, пустой.

F or i=1 to 5 ‘ присваивает значения переменной i в первый раз 1,

‘ затем прибавляя по 1 с каждым новым заходом в

‘ цикл. Значение 5 будет последним.

Sheets(i).Activate ‘ активируем очередной лист

Columns(1).Insert ‘ вставляем первый столбец

Next i ‘ конец цикла, то есть конец списка действий,

‘ которые нужно сделать 5 раз подряд

Цикл While

Цикл While используется чаще, чем For, потому что часто мы не знаем, сколько раз нужно сделать какое-то действие. Он используется, например, при поиске строчки с заданными критериями. Рассмотрим такой пример: в столбцах есть данные – фамилия (1 столбец), имя (2 столбец), отчество (3), год рождения (4). Нам нужно найти Иванова и, например, удалить его из базы. Допустим, мы знаем, что такой человек один. Тогда программа может быть такой:

i=2 ‘ определяем, с какой строки начинается база (в первой строке расположены заголовки)

Do While Cells(i,1)<>“Иванов” ‘ делать до тех пор, пока

В итоге цикл закончится на искомой строчке. После этого можно делать с этой строчкой всё, что угодно.

Или, может быть, мы не знаем, сколько Ивановых в нашей базе и хотим удалить их всех. Здесь нам понадобится условный оператор If

Оператор If

Условный оператор If (если) называется так, потому что проверяет выполнение условия. Принцип его работы понять очень просто: если условие выполняется, то делаем так, иначе делаем так. Рассмотрим наш пример: нам нужно пробежать по всей базе, то есть до тех пор, пока строчки не пусты, т.е:

Заметим, что важно, чтобы не было пропусков. Если в вашей базе есть строки с пустыми ячейками, то можно указывать более сложное условие, например

Do While С ells(i,1)<>“” and С ells(i+1,1)<>“” and С ells(i+2,1)<>“”

то есть нужно, чтобы три строки подряд были пустыми в первом столбце. Или

Do While С ells(i,1)<>“” and С ells(i,2)<>“” and С ells(i,3)<>“”

то есть нужно, чтобы в первом, втором и третьем столбцах строки было пусто.

Воспользуемся этим условием

Do While С ells(i,1)<>“” and С ells(i,2)<>“” and С ells(i,3)<>“”

If Cells ( i ,1)=”Иванов” then ‘ Если в строке Иванов

Rows(i).Delete ‘ тогда удаляем эту строку

i=i-1 ‘ возвращаемся на строку выше

End If ‘ закрываем условие

i=i+1 ‘ переходим к следующей строке

Обрати внимание на необходимость возвращаться на строку выше при удалении строки. В программе мы указываем номер строки, а переходим к следующей, прибавляя единичку. Если удалить строку номер 5, то на её место “въедут” данные шестой строки, став снова пятой. Но так как эти данные мы не просмотрели, придется зайти в строку №5 снова. Чтобы это сделать, после удаления отнимаем единичку, потом при переходе прибавляем единичку и попадаем в строку с тем же номером.

Источник: analitica.jimdofree.com

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

VBA циклы — For Next и For Each в Excel

Цикл For Loop в VBA – один из самых популярных циклов в Excel. Данный цикл имеет две формы – For Next и For Each In Next. Данные операторы используются для последовательного перемещения по списку элементов или чисел. Для завершения цикла мы можем в любой момент использовать команду выхода. Давайте подробнее рассмотрим каждый из этих циклов.

VBA цикл For Next

Цикл For Next имеет следующий синтаксис:

То что мы делаем здесь, по существу, это создаем цикл, который использует переменную счетчик как хранитель времени. Устанавливаем его значение равным начало_счетчика, и увеличиваем (или уменьшаем) на 1 во время каждого витка. Цикл будет выполняться до тех пор, пока значение счетчик не станет равным конец_счетчика. Когда оба эти значения совпадут, цикл выполнится последний раз и остановится.

Последнее значение переменной счетчик будет равным 11

VBA обратный цикл For Loop с инструкцией STEP

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

Последнее значение переменной счетчик будет равным 1.

Как вы могли заметить, мы можем использовать инструкцию Step n для работы цикла как вперед, так и в обратном направлении. По умолчанию значение Step равно 1, но оно может быть изменено, если необходимо пропускать какие-либо значения, тогда значение n будет больше одного, или перемещаться в обратном направлении, тогда n будет отрицательным.

VBA цикл For Each … Next

Цикл For Each … Next имеет следующий цикл:

Здесь переменная элемент_группы принадлежит к группе_элементов (железная логика. ). Я имею в виду, что объект группа_элементов должен быть коллекцией объектов. Вы не сможете запустить цикл For Each для отдельно объекта (Microsoft сразу оповестит вас об этом 438-й ошибкой).

Данный цикл перебирает все элементы какой-либо коллекции, начиная с самого первого. Вы можете использовать данный цикл, если вам необходимо, например, обойти все листы в книге, объекты на листе, сводные таблицы и т.д.

Ниже представлен пример, как можно воспользоваться циклом For Each для просмотра всех листов книги:

… либо всех сводных таблиц на листе

Прерывание цикла VBA

Если вам необходимо выйти из цикла до момента, как будет достигнуто условие завершения цикла, воспользуйтесь командой End For в связке с инструкцией IF. В примере, приведенном ниже, мы выйдем из цикла до момента достижения условия завершения, в данном цикле выход будет осуществлен при условии, когда переменная счетчик будет равна 3.

Пропуск части цикла в For Each

Пропускать часть цикла, а затем возвращаться назад – плохая практика. Тем не менее, давайте рассмотрим пример:

Здесь мы пропустили одну итерацию (когда j = 3). Как вы думаете, какой результат выдаст программа? 3? 5? Ну… на самом деле, ни один из вариантов не верный. Цикл будет выполняться бесконечно, пока память компьютера не переполнится.

Однако возможность пропустить шаг цикла без последствий существует. Вы можете увеличить значение счетчика на 1 (или другое значение), что приведет к пропуску операций, находящихся между этими значениями. Вот пример:

Но опять же, это плохая практика написания кода, и может привести к нежелательным последствиям при написании кода в будущем. Вместо этого, при необходимости пропуска некоторых итераций, попробуйте использовать функцию If или Select Case.

Вам также могут быть интересны следующие статьи

5 комментариев

Здравствуйте! Подскажите можно ли как нибудь сделать, чтобы цикл перемещался по столбцу и искал наименьшее значение, удалял его и снова искал и снова удалял. Остановиться должен лишь тогда, когда сумма значений в этом столбце не будет равна или меньше, какого то базового значения.

>Последнее значение переменной счетчик будет равным 1.
Вообще-то 0

Лажа какая-то, статья как делать не стоит!
НИкогда не пишите Goto.

Источник: exceltip.ru