Как сделать цикл в excel?

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

С первого взгляда задача нетрудная, но в ходе проектирования я понял, что избежать использования циклов не удастся.

Особую специфику здесь придавало условие заказчика, что готовый продукт должен быть именно

• Excel
• таблицей БЕЗ макросов и дополнительных модулей (функций пользователя).
• Стандартный набор функций Excel не поддерживает ничего похожего на циклы, однако выход нашелся – использование циклических ссылок и итераций.
• Первый шаг, чтобы начать работать с циклическими ссылками – это разрешить итеративные вычисления в Excel. В версии Microsoft Office XP это делается в разделе меню «Сервис – Параметры»:

В открывшемся окне активируем соответствующий флажок:

Так же обратите внимание на поле «Предельное число итераций» — оно определяет, сколько раз будет проходить вычисление по всем циклическим ссылкам, если вы сами не поставите ограничение.

!Информация: В Excel 2007 включение итеративных вычислений производится в пункте меню «Файл – Параметры Excel» в разделе «Формулы».

1. Рассмотрим пример использования итеративных вычислений в виде цикла.
2. Впишите в ячейку А1 формулу:
3. Если значение предельного числа итераций равно 100, то результатом вычислений в ячейке А1 будет равно 100. Теперь изменим формулу:

Как видите число итераций мы ограничили числом в ячейке А2, а именно 10. Чтобы проиллюстрировать, как происходят итерации, добавим формулу в ячейку B2:

Как видите, каждый этап итерации (новое значение А1) добавляется к ячейке B1 до тех пор пока происходят итерации.

!Замечание: Возможно, результат в B2 будет не таким – для того, чтобы исправить это сделайте следующее: после ввода формулы в ячейку B1 перейдите в ячейку A1, установите курсор в поле редактирования формулы и нажмите Enter – произойдет пересчет итераций.

Для корректной работы итераций необходимо задать ряд дополнительных условий, ограничивающих их количество и задающее условие сброса текущего значения поля в исходное значение.

Пересчет всех ячеек с циклическими ссылками происходит каждый раз при изменении ячеек, от которых они зависят.

!Дополнительные ссылки:

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

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

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

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

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

Цикл «For … Next»

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

For i = 1 To 10 Total = Total + iArray(i) Next i

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

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

For d = 0 To 10 Step 0.1 dTotal = dTotal + d Next d

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

For i = 10 To 1 Step -1 iArray(i) = i Next i

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

Цикл «For Each»

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

Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox «Найден лист: » & wSheet.Name Next wSheet

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

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

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

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

For i = 1 To 100 If dValues(i) = dVal Then IndexVal = i Exit For End If Next i

Цикл «Do While» в Visual Basic

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

'Процедура Sub выводит числа Фибоначчи, не превышающие 1000 Sub Fibonacci() Dim i As Integer 'счётчик для обозначения позиции элемента в последовательности Dim iFib As Integer 'хранит текущее значение последовательности Dim iFib_Next As Integer 'хранит следующее значение последовательности Dim iStep As Integer 'хранит размер следующего приращения 'инициализируем переменные i и iFib_Next i = 1 iFib_Next = 0 'цикл Do While будет выполняться до тех пор, пока значение 'текущего числа Фибоначчи не превысит 1000 Do While iFib_Next < 1000 If i = 1 Then 'особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else 'сохраняем размер следующего приращения перед тем, как перезаписать 'текущее значение последовательности iStep = iFib iFib = iFib_Next End If 'выводим текущее число Фибоначчи в столбце A активного рабочего листа 'в строке с индексом i Cells(i, 1).Value = iFib 'вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

В приведённом примере условие iFib_Next < 1000 проверяется в начале цикла. Поэтому если бы первое значение iFib_Next было бы больше 1000, то цикл бы не выполнялся ни разу.

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

Схематично такой цикл Do While с проверяемым условием в конце будет выглядеть вот так:

Do … Loop While iFib_Next < 1000

Цикл «Do Until» в Visual Basic

Цикл Do Until очень похож на цикл Do While: блок кода в теле цикла выполняется раз за разом до тех пор, пока заданное условие выполняется (результат условного выражения равен True). В следующей процедуре Sub при помощи цикла Do Until извлекаются значения из всех ячеек столбца A рабочего листа до тех пор, пока в столбце не встретится пустая ячейка:

iRow = 1 Do Until IsEmpty(Cells(iRow, 1)) 'Значение текущей ячейки сохраняется в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop

В приведённом выше примере условие IsEmpty(Cells(iRow, 1)) находится в начале конструкции Do Until, следовательно цикл будет выполнен хотя бы один раз, если первая взятая ячейка не пуста.

Однако, как было показано в примерах цикла Do While, в некоторых ситуациях нужно, чтобы цикл был выполнен хотя бы один раз, не зависимо от первоначального результата условного выражения. В таком случае условное выражение нужно поместить в конце цикла, вот так:

Do … Loop Until IsEmpty(Cells(iRow, 1))

• Урок подготовлен для Вас командой сайта office-guru.ru Источник: /> Перевел: Антон Андронов
• Правила перепечаткиЕще больше уроков по Microsoft Excel
• Оцените качество статьи. Нам важно ваше мнение:
• Добрый день!Неделю пытаюсь разобраться с макросами в Excel — выходит признаюсь честно плохо…

Решение задачи так и не нашла. Надеюсь вы сумеете мне подсказать куда конкретнее направить свою активность.

Источник: http://word-office.ru/kak-sdelat-cikl-v-excel.html

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

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

https://www.youtube.com/watch?v=-abtcFy-9vc

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

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

    Exit For

Next счетчик

где,

• «счетчик» — переменная, которая изменяется на указанный «шаг». Если шаг не указан, то по умолчанию берется единица.
• «начало цикла», «конец цикла» — числа или переменные указывающие нижний предел счетчика и верхний. Остановка цикла происходит тогда, когда «счетчик» > «конец цикла» (или, если цикл обратный, т.е. с шагом -1, то  «счетчик» < "конец цикла").
• Exit For – команда принудительной остановки цикла. Применяется в тех случаях, когда произошло некоторое событие, после которого необходимо остановить выполнение команд в цикле, или для предотвращения возникновения ошибки.

Рассмотрим пару примеров использования цикла For. В дальнейшем, с этим циклом будем встречаться довольно часто.

Пример 1Даны два столбца С и Е заполненные числами:

Необходимо сложить числа в столбце С с числами столбца Е следующим образом:С2+Е21, С3+E20, …, C21+E2. Результат вывести в столбец D в виде формулы т.е. содержание ячейки результата должно быть «=С2+Е21».

Код макроса выглядит следующим образом (куда прописывать код читаем здесь):

Sub Цикл_For()'константа указывающая предел цикла т.е. до какого значения циклу бежать

Const n = 21

    For i = 2 To n    ' создаем строку формулу и сохраняем ее в ячейку

•     Cells(i, 4) = «=C» & CStr(i) & «+E» & CStr((n — i) + 2)
•   Next i
• End Sub
• Разбираем написанный код:

    ' продолжение когда выполняющегося в цикле    ' остальной код программы

• Const n = 21 — описание константы n со значением 21, т.е. число строк по которому необходимо пробежаться циклу For;
• For i = 2 To n — i счетчик который будет изменяться на 1 с каждым проходом цикла. Счетчик начинается с 2 и заканчивается когда i>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 iEnd Sub

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

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

Прикрепленный файл: work_for.zip

Источник: https://www.programm-school.ru/rabota_s_ciklom_for_vba.html

Не волнуйтесь, я сейчас все объясню!

03 Ноя 2009

Использование макросов циклов

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

Цикл Do…Loop

В цикле Do…Loop производится подсчет строк в диапазоне данных до обнаружения пустой строки.

Предположим, вы хотите подсчитать количество строк в диапазоне данных, который может быть небольшим, а может — весьма солидным. Для этого следует использовать цикл Do…Loop. Он выполняет заданное действие столько раз, сколько необходимо. При этом подсчитывается число всех строк, найденных в диапазоне.

Или предположим, что требуется выполнить одно действие в двух диапазонах данных, содержащих различное число строк. И в этом случае будет полезен цикл Do…Loop. Он выполнится столько раз, сколько необходимо для каждого из диапазонов. Как цикл определит, сколько именно раз следует повторить действие? Из заданных вами условий.

Повтор прекратится, как только обнаружится определенный фрагмент данных, например пустая строка или некий текст.

В качестве условия выхода из цикла Do…Loop применяется условие While или условие Until. Цикл выполняется, пока заданное утверждение остается истинным (While) или пока оно не становится истинным (Until). Так, чтобы задать цикл, прекращающийся при обнаружении пустой ячейки в первом столбце, следует использовать условие While:

Если цикл требуется выполнять до обнаружения ячейки, содержащей число 365, используйте условие Until. В любом случае именно вы предоставляете программе сведения, помогающие ей определить, когда следует выйти из цикла.

Цикл For Each…Next

Цикл For Each…Next позволяет выделить более темным шрифтом каждое слово «ОК» в тексте выбранного фрагмента.

Цикл For Each…Next используется для выполнения некоторого действия над каждой ячейкой диапазона данных. Предположим, например, что в выделенном диапазоне требуется повсюду изобразить слово «ОК» более темным шрифтом по сравнению с остальным текстом. Код будет выглядеть примерно следующим образом:

Здесь «MyCell» указывает текущую ячейку, обрабатываемую в цикле, а «For Each» означает, что в цикле перебираются все ячейки выделенного фрагмента. При обнаружении ячейки, содержащей только слово «ОК», это слово выделяется темным шрифтом. (Внешний вид текста контролируется свойством Font, а атрибут Bold означает полужирное начертание.)

Вложенные циклы

Циклы Do…Loop и For Each…Next весьма эффективны в простейших случаях. Теперь мы немного повысим уровень сложности, перейдя к рассмотрению вложенных циклов. Они используются, когда необходимо выполнить какое-либо действие в нескольких диапазонах данных или несколько раз в одном диапазоне данных.

Аналогией вложенных циклов может служить вращение Земли вокруг Солнца. Один полный оборот вокруг Солнца (год) можно считать внешним циклом, а один оборот Земли вокруг своей оси (сутки) — внутренним циклом, вложенным во внешний цикл.

В течение каждого года выполняется 365 внутренних циклов, и каждое 1 января внешний цикл повторяется:

Разумеется, этот код не будет выполняться в Excel, но он наглядно показывает, что каждый большой цикл (оборот вокруг Солнца) включает 365 маленьких циклов (оборотов Земли вокруг своей оси).

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

Для этого достаточно сравнить первую строку с каждой последующей и удалить все дубликаты, если они будут обнаружены, затем сравнить вторую строку с каждой последующей и т. д.

Внешний цикл обеспечивает повторение этого процесса для каждой строки. Во внутреннем цикле будет производиться фактическое сравнение и удаление строк.

Использование свойства Cells в циклах

Столбцы обозначаются числами, а не буквами.

Данные, содержащиеся в ячейке, можно получать в коде макроса цикла двумя разными способами. Один из них предусматривает использование свойства Cells, а второй — использование свойства Range. В VBA обычно бывает легче и удобнее работать со свойством Cells, поскольку изменять описываемые им значения проще.

Свойство Range идентифицирует строки и столбцы на листе с помощью чисел и букв, а в свойстве Cells и строки, и столбцы обозначаются числами. Добавление +1 к таким числам позволяет легко переходить в цикле от строки к строке и от столбца к столбцу, а вот реализовать в коде переход от одной буквы к следующей за ней не так просто.

Совет. При необходимости в электронной таблице можно задать отображение номеров столбцов вместо буквенных обозначений. Выберите в меню Сервис команду Параметры и перейдите на вкладку Общие. Установите флажок «Стиль ссылок R1C1». Если позже потребуется вернуть прежние обозначения, снимите этот флажок.

Упражнение

Для выполнения упражнения загрузите файл macros1

Упражнение 1. Подсчет с использованием цикла Do…Loop

Цикл Do…Loop будет использован для подсчета количества строк в диапазоне данных.

1. Откройте файл практического занятия, выделите код в первом текстовом поле листа (от строки Sub CountRows() до End Sub) и скопируйте его в буфер обмена.Совет. После запуска редактора Visual Basic данные инструкции будут закрыты новым окном. Чтобы инструкции были всегда доступны, напечатайте эту страницу (щелкните правой кнопкой мыши и выберите в контекстном меню команду Печать).В этом фрагменте кода переменной x присваивается номер текущей обрабатываемой строки; x + 1 обозначает следующую строку. Переменной z присваивается текущее число подсчитанных строк, начиная с 0, когда еще ничего не было сосчитано. Цикл обрабатывает поочередно каждую строку, пока не дойдет до пустой строки. При каждом переходе к следующей строке число строк, записанное в переменной z, увеличивается на 1. В итоге подсчитанное количество строк выводится в сообщении; это значение берется из переменной z.Значение z отображается в сообщении в окружении двух текстовых фрагментов. Все это объединяется в одну фразу с помощью знаков &.
2. Щелкните вне текстового поля с кодом и выделите ячейку C3 (или, если включен стиль ссылок R1C1, выделите строку 3 и столбец 3).
3. Откройте редактор Visual Basic и вставьте новый модуль.Как это сделать? Выделите в меню Сервис пункт Макрос и выберите команду Редактор Visual Basic. В меню редактора Insert выберите команду Module.
4. Вставьте код, скопированный на шаге 1, в новый модуль.
5. Щелкните в строке Sub CountRows() и нажмите клавишу F5, чтобы выполнить макрос.
6. Цикл Do…Loop выполняет необходимые действия, и на экране появляется сообщение о том, что в заданном диапазоне содержится шесть строк.
7. Нажмите кнопку ОК, чтобы закрыть окно сообщения, и вернитесь в Excel, выбрав в меню File команду Close and Return to Microsoft Excel.

Примечание. В этом упражнении переменная z хранит текущее число строк, x — текущий номер строки, а y — номер столбца (в данном случае это номер 3, что соответствует столбцу C).

Упражнение 2. Подсчет ячеек с использованием цикла For Each…Next

1. Выделите код во втором текстовом поле листа (начиная со строки Sub CountCells() и до End Sub) и скопируйте его в буфер обмена.Подсказка. Чтобы увидеть второе текстовое поле, начинающееся со строки 27, возможно, потребуется прокрутить лист.

   https://www.youtube.com/watch?v=qBwwn6ESeCM

   В этом фрагменте кода переменной z присваивается подсчитанное количество строк, начиная с 0, когда еще ничего не было сосчитано. С помощью этого кода поочередно перебираются все ячейки выделенного диапазона. При каждом переходе к следующей ячейке их общее число, записываемое в переменной z, увеличивается на 1.

   В итоге подсчитанное количество ячеек выводится в сообщении; это значение берется из переменной z.

2. Щелкните вне текстового поля с кодом и выделите диапазон C3:D8, значения от «Светлана» до «Шашков». Если включен стиль ссылок R1C1, выделите фрагмент со строки 3, столбца 3 до строки 8, столбца 4.

3. Откройте редактор Visual Basic и вставьте новый модуль.Подсказка. О том, как это сделать, см. в первом упражнении.
4. Вставьте код, скопированный на шаге 1, в новый модуль.
5. Щелкните в строке Sub CountCells() и нажмите клавишу F5.

6. Готово! Появляется сообщение о том, что в выделенном фрагменте содержится 12 ячеек.
7. Нажмите кнопку ОК, чтобы закрыть окно сообщения. Закройте редактор Visual Basic и книгу. Будет предложено сохранить книгу; можете сделать это, если хотите вернуться к ней позже.

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

Примечание. Если появится сообщение Microsoft Visual Basic «Compile error» (ошибка компиляции), не беспокойтесь. Это просто означает, что код в модуле не в точности совпадает с кодом, который было предложено скопировать и вставить. Удалите код из модуля и скопируйте его туда снова.

Понравился материал? Подпишитесь на обновления сайта.

Мой блог находят по следующим фразам

• майкрософт офис 2007 скачать бесплатно

• программа для востоновления данных после быстрого форматирование
• сайты торренты бесплатные
• компьютерные курсы в Москве для пенсионеров
• office 2007 rus скачать одним файлом бесплатно
• программа для востановления данных после форматирования

• ***
  Постовой
• недорогой =репетитор= по математике
• Возможно, Вас заинтересует также информация по следующим ключевым словам, которую обычно ищут на моем сайте • включение компьютера по сети • скачать microsoft office 2007 • программы для ограничения доступа детей в интернет • Скачать програму для ноутбука для электросети • автоматическое включение и выключение компьютера • программы для восстановление флешки после форматирования

Источник: http://shperk.ru/uroki/ispolzovanie-makrosov-microsoft-excel-dlya-sozdaniya-ciklov.html/2

Как создать именованные диапазоны, используя цикл в Excel?

• Я хотел бы создать именованные диапазоны, используя для сценария петли в Excel.
• 
• Моя цель состоит в том, чтобы создать диапазоны для каждой строки из В до D столбцов, которые называются после колоночной Aeg: шкала «alfae» содержит B1: D1, и диапазон «alfag» содержит В2: D2, и так далее.
• Вот основной сценарий, который я создал:

Sub ExampleMacro()
'
' ExampleMacro Macro
'
'
Range(«B1:D1″).Select
ActiveWorkbook.Names.Add Name:=»alfae», RefersToR1C1:=»=Munka1!R1C2:R1C4″
End Sub

Мой вопрос: как я могу Перебери каждую строку в колонке А? И как я могу назвать диапазон со значением ячейки (например, A1: D1 = «alfae», A2: D2 = «alfag»; и т.д.)?

Заранее спасибо.

Что-то вроде этого будет вполне достаточно:

Sub TestMe()

Dim myRange As Range
Dim myRow As Range

Set myRange = Range(«A1:D4»)

For Each myRow In myRange.Rows
If Not IsNumeric(Left, Cells(myRow.Row, 1)) Then
ActiveWorkbook.Names.Add Name:=Cells(myRow.Row, 1), _
RefersToR1C1:=Range(myRange(myRow.Row, 2), myRange(myRow.Row, myRow.Columns — 1))
End If
Next myRow

End Sub

Просто убедитесь , что у вас есть какое — то значение A1:A4. Вы можете пойти дальше, обновление кода. Left()Используется в качестве условия, поскольку указанный диапазон не может начинаться с цифры.

Sub MakeNamedRanges()

startrow = 1
endrow = Cells(Rows.Count, «A»).End(xlUp).Row

For r = startrow To endrow
Range(Cells(r, 2), Cells(r, 4)).Select
rangename = Cells(r, 1)
ActiveWorkbook.Names.Add Name:=rangename, RefersToR1C1:=»=Munka1!R» & r & «C2:R» & r & «C4»
Next r

End Sub

sub Example()
Dim r as range
set r = range(«A1″)
Do
ActiveWorkbook.Names.Add Name:=r.text, RefersToR1C1:=»=Munka1!R» & r.row & «C2:R» & r.row & «C4»
set r = r.offset(1.0)
Loop until r = «»
End SUb

Вам не нужно в цикле:

Sub ExampleMacro()
Dim r As Range
Set r = Application.Intersect(Sheet4.Range(«A1»).CurrentRegion, Sheet4.Range(«A:D»))
r.CreateNames Left:=True
End Sub

С помощью предлагаемых имен в колонке А , попробуйте:

Sub WhatsInAName()
For Each r In Columns(1).SpecialCells(2)
Range(r.Offset(, 1), r.Offset(, 3)).Name = r.Value
Next r
End Sub

EDIT # 1:

Для того, чтобы сохранить некоторые печатать, вы можете заменить:

Range(r.Offset(, 1), r.Offset(, 3)).Name = r.Value

с:

r.Offset(, 1).Resize(, 3).Name = r.Value

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

Источник: https://askvoprosy.com/voprosy/how-to-create-named-ranges-using-for-loop-in-excel

Циклы VBA: for, while и другие

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

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

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

Цикл For в VBA

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

Dim mas(5) As Integer
For i% = 0 To 4 mas(i) = Int((10 * Rnd) + 1)
Next i

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

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

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

Function expr(x As Integer) As Integer expr = 5 * x + 5
End Function
Sub CodeTown() Dim i As Integer For i = -10 To 10 Step 1 If expr(i) = 0 Then MsgBox «При Y = 0, Х = «+ CStr(i) Next i
End Sub

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

Function expr(x As Integer) As Integer expr = 5 * x + 5
End Function
Sub CodeTown() Dim i As Integer For i = -10 To 10 Step 1 If expr(i) = 0 Then MsgBox «При Y = 0, Х = «+ CStr(i) Exit For End If Next i
End Sub

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

Цикл For Each в VBA

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

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

For Each ws In ThisWorkbook.Worksheets MsgBox ws.Name
Next ws

1. И ещё один интересный пример:
2. Изменить размер шрифта и выравнить по центру текст в label

For Each element In UserForm1.Controls If InStr(1, UserForm1.Controls.Item(i%).Name, «Label») > 0 Then UserForm1.Controls.Item(i%).TextAlign = fmTextAlignCenter UserForm1.Controls.Item(i%).Font.Size = 20 i% = i% + 1 End If
Next element

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

Цикл While в VBA

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

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

Dim mas(5) As Integer
For i% = 0 To 4 mas(i%) = Int((10 * Rnd) + 1)
Next i
Dim count As Integer, temp As Integer
count = 1
Do While count > 0 count = 0 For i% = 0 To 3 If mas(i) > mas(i + 1) Then temp = mas(i) mas(i) = mas(i + 1) mas(i + 1) = temp count = count + 1 End If Next i%
Loop

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

Цикл Until в VBA

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

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

Dim temp As Variant
Do temp = InputBox(«Введите число»)
Loop Until IsNumeric(temp)

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

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

Похожее

Источник: https://codetown.ru/vba/cikly/

Как сделать цикл по колонкам в Excel?

← →gsvserg   (2003-08-07 18:49) [0]

Нужно в зависимости от длины поля в таблице, которая копируется в Excel, менять column.width в Excel Что-то типа for i:=0 to 25 do if a>b then worksheet.columns[i].width:=…

← →malamba   (2003-08-07 19:10) [1]

Что за таблица? И что за длина поля?

← →gsvserg   (2003-08-07 19:23) [2]

Таблица AdoQuery, в ней несколько полей. Все поля строковые. Перекидываю ее в Excel. Вычисляю максимальную длину строки в каждом поле, после нужно установить ширину колонок в Excel, чтобы было видно всю строку.

← →Dred2k   (2003-08-07 20:16) [3]

Вот тебе кусок кода. for j := Low(tmpColNum) to High(tmpColNum) do begin App.Columns[j + 1].ColumnWidth := FDBGrid.Columns[tmpColNum[j]].Width / 6; App.Cells[1, j + 1].Value := FDBGrid.Columns[tmpColNum[j]].Title.Caption; App.

Color := RGB(0, 0, 0);

( я поленился, наверное 😉

Вот тебе кусок кода.
for j := Low(tmpColNum) to High(tmpColNum) do begin
App.Columns[j + 1].ColumnWidth := FDBGrid.Columns[tmpColNum[j]].Width / 6;
App.Cells[1, j + 1].Value := FDBGrid.Columns[tmpColNum[j]].Title.Caption;
App.Cells[1, j + 1].HorizontalAlignment := DefExcelAlignment[FDBGrid.Columns[tmpColNum[j]].Title.Alignment];
App.Cells[1, j + 1].Font.Bold := True;
App.Cells[1, j + 1].Interior.Color := RGB(192, 192, 192);
App.Cells[1, j + 1].Borders.Color := RGB(0, 0, 0);
end;
tmpColNum — массив, содержащий номера выводимых мной колоной DBGrid-а. Ширина колонок в Excel выставляется соразмерно ширине колонок грида. Коэффициент соразмерности можно при желании сформулировать и в общем виде (я поленился, наверное ;)).

Кроме ширины выставляются свойства первой строки на листе (заголовок). Лист — текущий.

← →Alex Bredin   (2003-08-08 10:44) [4]

я тож пытался так сделать, но потом сообразил, что Excel сделает это лучше)) //пробуем применить автоформат… Range1:=Sheet.Range[Sheet.Cells[StartR, StartC],Sheet.Cells[StartR + Rows, StartC + Cols — 1]]; Range1.AutoFormat(XLFormat,True,True,True,True,True,True); XLFormat-цел.число(я беру 1)-посмотри

← →Alex Bredin   (2003-08-08 10:50) [5]

а вообще над Range можно извращаться как хочешь, только перед этим включи запись макроса, а потом перенеси его текст в Delphi

← →gsvserg   (2003-08-08 11:43) [6]

Alex Bredin © (08.08.03 10:44) Спасибо, попробовал твой метод — работает. Только в Excel появляются линии под первой и предпоследней строками. Я так понял что это из-за xlFormat=1. Всем спасибо.

← →Alex Bredin   (2003-08-08 14:53) [7]

понял ты неправильно. Цитата из экселевского макроса: Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _ :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True думаю все ясно и так

← →gsvserg   (2003-08-08 15:35) [8]

Range1.AutoFormat(1,True,True,true,false,True,True); thanxxxx

Источник: http://delphimaster.net/view/3-1316/all