Как сделать цикл в vba по столбцу

Цикл For… Next в VBA Excel, его синтаксис и описание отдельных компонентов. Примеры использования цикла For… Next.

Цикл For… Next в VBA Excel предназначен для выполнения группы операторов необходимое количество раз, заданное управляющей переменной цикла – счетчиком. При выполнении цикла значение счетчика после каждой итерации увеличивается или уменьшается на число, указанное выражением оператора Step, или, по умолчанию, на единицу. Когда необходимо применить цикл к элементам, количество которых и индексация в группе (диапазон, массив, коллекция) неизвестны, следует использовать цикл For Each… Next.

Синтаксис цикла For… Next

For counter = start To end [ Step step ]

[ ements ]

[ Exit For ]

[ ements ]

Next [ counter ]

For счетчик = начало To конец [ Step шаг ]

[ операторы ]

[ Exit For ]

[ операторы ]

Next [ счетчик ]

В квадратных скобках указаны необязательные атрибуты цикла For… Next.

Компоненты цикла For… Next

Компонент	Описание
counter	Обязательный атрибут. Числовая переменная, выполняющая роль счетчика, которую еще называют управляющей переменной цикла.
start	Обязательный атрибут. Числовое выражение, задающее начальное значение счетчика.
end	Обязательный атрибут. Числовое выражение, задающее конечное значение счетчика.
Step*	Необязательный атрибут. Оператор, указывающий, что будет задан шаг цикла.
step	Необязательный атрибут. Числовое выражение, задающее шаг цикла. Может быть как положительным, так и отрицательным.
ements	Необязательный** атрибут. Операторы вашего кода.
Exit For	Необязательный атрибут. Оператор выхода из цикла до его окончания.
Next [ counter ]	Здесь counter – необязательный атрибут. Это то же самое имя управляющей переменной цикла, которое можно здесь не указывать.

*Если атрибут Step отсутствует, цикл For… Next выполняется с шагом по умолчанию, равному 1.

**Если не использовать в цикле свой код, смысл применения цикла теряется.

Примеры циклов For… Next

Вы можете скопировать примеры циклов в свой модуль VBA, последовательно запускать их на выполнение и смотреть результаты.

Простейший цикл

Заполняем десять первых ячеек первого столбца активного листа Excel цифрами от 1 до 10:

Sub test1()

Dim i As Long

For i = 1 To 10

Cells(i, 1) = i

End Sub

Простейший цикл с шагом

В предыдущий цикл добавлен оператор Step со значением 3, а результаты записываем во второй столбец:

Sub test2()

Dim i As Long

For i = 1 To 10 Step 3

Cells(i, 2) = i

End Sub

Цикл с отрицательными аргументами

Этот цикл заполняет десять первых ячеек третьего столбца в обратной последовательности:

Sub test3()

Dim i As Long

For i = 0 To -9 Step -1

Cells(i + 10, 3) = i + 10

End Sub

Увеличиваем размер шага до -3 и записываем результаты в четвертый столбец активного листа Excel:

Sub test4()

Dim i As Long

For i = 0 To -9 Step -3

Cells(i + 10, 4) = i + 10

End Sub

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

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

Sub test5()

Dim i1 As Long, i2 As Long

For i1 = 1 To 10

‘Пятой ячейке в строке i1 присваиваем 0

Cells(i1, 5) = 0

For i2 = 1 To 4

Cells(i1, 5) = Cells(i1, 5) + Cells(i1, i2)

End Sub

Выход из цикла

В шестой столбец активного листа запишем названия десяти животных, конечно же, с помощью цикла For… Next:

Sub test6()

Dim i As Long

For i = 1 To 10

Cells(i, 6) = Choose(i, “Медведь”, “Слон”, “Жираф”, “Антилопа”, _

“Крокодил”, “Зебра”, “Тигр”, “Ящерица”, “Лев”, “Бегемот”)

End Sub

Следующий цикл будет искать в шестом столбце крокодила, который съел галоши. В ячейку седьмого столбца цикл, пока не встретит крокодила, будет записывать строку «Здесь был цикл», а когда обнаружит крокодила, запишет «Он съел галоши» и прекратит работу, выполнив команду Exit For. Это будет видно по ячейкам рядом с названиями животных ниже крокодила, в которых не будет текста «Здесь был цикл».

Sub test7()

Dim i As Long

For i = 1 To 10

If Cells(i, 6) = “Крокодил” Then

Cells(i, 7) = “Он съел галоши”

Exit For

Else

Cells(i, 7) = “Здесь был цикл”

End If

End Sub

Результат работы циклов For… Next из примеров:

Результат работы циклов For... Next из примеров

Результат работы циклов For… Next

Такие данные на активном листе Excel вы получите, если последовательно запустите на выполнение в редакторе VBA все семь подпрограмм из примеров, демонстрирующих работу циклов For… Next.

Цикл с дробными аргументами

Атрибуты start, end и step могут быть представлены числом, переменной или числовым выражением:

For i = 1 To 20 Step 2

For i = a To b Step c

For i = a – 3 To 2b + 1 Step c/2

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

‘Значения атрибутов до округления

For i = 1.5 To 10.5 Step 2.51

‘Округленные значения атрибутов

For i = 2 To 10 Step 3

Старайтесь не допускать попадания в тело цикла For… Next неокругленных значений аргументов, чтобы не получить непредсказуемые результаты его выполнения. Если без дробных чисел не обойтись, а необходимо использовать обычное округление, применяйте в коде VBA функцию рабочего листа Worksheet.Round для округления числа перед использованием его в цикле For… Next.

Источник

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

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

Цикл For
Цикл Do While
Цикл Do Until

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

Оператор цикла «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 по умолчанию используется приращение 1. Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step, как показано в следующем простом примере.

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

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

Для определения шага цикла в 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))

Оцените качество статьи. Нам важно ваше мнение:

Источник

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

i = 1 Do Range(“E” & i & “:D” & i).Select i = i + 1 Loop Until i > 10

но что, если мы хотим сделать цикл на колонке?

Можем ли мы использовать тот же метод, что и выше?

в то время как столбцы в Excel-это комплекс, такой как A, B, C,…, Y, Z, AA, AB, AC, … и т. д. проблемы будут возникать между циклами от “Z” до “AA”.

как мы делаем цикл алфавитного столбца от “A” до “Z”, а затем продолжаем в “AA”, “AB” и так далее

есть ли что-нибудь, что может помочь?

vba excel

Источник wahyueka31 21 декабря 2012 в 06:03

3 ответа

Цикл по списку строк в excel VBA
Я пытаюсь написать макрос, который при запуске на листе excel выберет определенный столбец и заменит все экземпляры списка строк списком других конкретных строк. Например, любой экземпляр string a1 будет заменен string a2, any string b1 на string b2и т. д. Поэтому после выбора столбца мне нужно…
VBA логические заголовки строк к столбцам Excel
У меня возникли проблемы с логикой некоторого кода VBA, и из-за этого я действительно не могу предоставить какой-либо код, потому что в нем отсутствует слишком много частей.. В основном то, что мне нужно сделать, это что-то изменить из этого: A x1 x2 x3 B y1 y2 y3 Into this: A x1 A x2 A x3 A B y1…

Да, давайте возьмем в качестве примера Select

пример кода: Columns(“A”).select

Как организовать цикл по столбцам:

Метод 1: (Вы можете использовать индекс для замены адреса Excel)

For i = 1 to 100 Columns(i).Select next i

Способ 2: (используя адрес)

For i = 1 To 100 Columns(Columns(i).Address).Select Next i

EDIT: Разделите колонку на OP

columnString = Replace(Split(Columns(27).Address, “:”)(0), “$”, “”)

например, вы хотите получить 27-й столбец –> AA, вы можете получить его таким образом

Larry 21 декабря 2012 в 06:31

Еще один способ попробовать. Кроме того, select может быть заменен при установке начального столбца в объект диапазона. С точки зрения производительности это помогает.

Dim rng as Range Set rng = WorkSheets(1).Range(“A1”) ‘– you may change the sheet name according to yours. ‘– here is your loop i = 1 Do ‘– do something: e.g. show the address of the column that you are currently in Msgbox rng.offset(0,i).Address i = i + 1 Loop Until i > 10

** два метода получения имени столбца с помощью столбца number**

Split()

код

colName = Split(Range.Offset(0,i).Address, “$”)(1)

Манипуляция строками:

код

myColName(colNum as Long) as String myColName = Left(Range(0, colNum).Address(False, False), _ 1 – (colNum > 10)) End

bonCodigo 21 декабря 2012 в 08:40

Если вы хотите придерживаться того же рода цикла, то это будет работать:

Option Explicit Sub selectColumns() Dim topSelection As Integer Dim endSelection As Integer topSelection = 2 endSelection = 10 Dim columnSelected As Integer columnSelected = 1 Do With Excel.ThisWorkbook.ActiveSheet .Range(.Cells(columnSelected, columnSelected), .Cells(endSelection, columnSelected)).Select End With columnSelected = columnSelected + 1 Loop Until columnSelected > 10 End Sub

EDIT

Если на самом деле вы просто хотите перебрать каждую ячейку в области электронной таблицы, то используйте что-то вроде этого:

Sub loopThroughCells() ‘============= ‘this is the starting point Dim rwMin As Integer Dim colMin As Integer rwMin = 2 colMin = 2 ‘============= ‘============= ‘this is the ending point Dim rwMax As Integer Dim colMax As Integer rwMax = 10 colMax = 5 ‘============= ‘============= ‘iterator Dim rwIndex As Integer Dim colIndex As Integer ‘============= For rwIndex = rwMin To rwMax For colIndex = colMin To colMax Cells(rwIndex, colIndex).Select Next colIndex Next rwIndex End Sub

whytheq 21 декабря 2012 в 08:04

Python цикл медленнее, чем Excel VBA?
Я провел небольшой тест между excel (VBA) и python, выполняя простой цикл. Код указан ниже. К моему удивлению, vba был значительно быстрее, чем python. Почти в 6 раз быстрее. Я думал, что из-за того, что python работает через командную строку, производительность будет лучше. Ребята, у вас есть…
Нужен простой цикл Excel VBA
Я пытаюсь сделать простой цикл в Excel VBA, который захватывает информацию из строки и копирует ту же информацию в следующую строку (или вставляет новую) в зависимости от значения в конкретной ячейке. Например, строка 1 имеет следующие значения (дом 1а, 3 уровня) затем создайте результат в ячейке…

4 ответа

Лучший ответ

Sub ctParts() Dim wsSrc As Worksheet: Set wsSrc = Worksheets(“Sheet1”) Dim wsDest As Worksheet: Set wsDest = Worksheets(“Sheet2”) Dim LastRow As Long: LastRow = wsSrc.UsedRange.Find(“*”, Order:=xlByRows, Direction:=xlPrevious).Row Dim LastCol As Long: LastCol = wsSrc.UsedRange.Find(“*”, Order:=xlByColumns, Direction:=xlPrevious).Column Dim i As Long, j As Long, RowCounter As Long: RowCounter = 2 With wsDest .Cells(1, 1) = “Order Number” .Cells(1, 2) = “Part Number” For i = 1 To LastRow For j = 3 To LastCol If wsSrc.Cells(i, j) <> “” Then .Cells(RowCounter, 1) = wsSrc.Cells(i, 2) .Cells(RowCounter, 2) = wsSrc.Cells(i, j) RowCounter = RowCounter + 1 End If Next j Next i End With End Sub

Tragamor 16 Мар 2017 в 15:52

Вот один подход

Sub x() Dim r As Long, c As Long Application.ScreenUpdating = False With Sheets(1) For r = 1 To .Range(“B” & Rows.Count).End(xlUp).Row c = .Cells(r, Columns.Count).End(xlToLeft).Column – 2 .Cells(r, 2).Copy Sheets(2).Range(“A” & Rows.Count).End(xlUp)(2).Resize(c) .Cells(r, 3).Resize(, c).Copy Sheets(2).Range(“B” & Rows.Count).End(xlUp)(2).PasteSpecial Transpose:=True Next r End With Application.ScreenUpdating = True End Sub

SJR 16 Мар 2017 в 15:31

Вы могли бы использовать подход Dictionary

Sub main() Dim cell As Range Dim var As Variant With CreateObject(“Scripting.Dictionary”) ‘<–| instantiate a ‘Dictionary’ object For Each cell In Worksheets(“Sheet1”).Range(“A1”, Worksheets(“Sheet1”).cells(Worksheets(“Sheet1”).Rows.Count, 1).End(xlUp)) ‘<– loop through “Sheet1” column A cells from row 1 down to the last not empty one var = Split(cell.Value, “,”) ‘<–| store current cell content into an array, whose first element will be the ‘key’ of the dictionary .item(var(0)) = Split(Replace(cell.Value, var(0) & “,”, “”, , 1), “,”) ‘<–| up current ‘key’ dictionary item with the array of “remaining” values Next For Each var In .Keys ‘<–| loop through dictionary keys Set cell = Worksheets(“Sheet2”).cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(.item(var)) + 1) ‘<–| set “Sheet2” range to start writing the current key values from cell.Value = var ‘<–| write key cell.Offset(, 1).Value = Application.Transpose(.item(var)) ‘<–| write current key values Next End With ‘<–| release ‘Dictionary’ object End Sub

user3598756 16 Мар 2017 в 16:40

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

Dim rng As Range Dim x As Variant Dim i As Long Dim offs As Long offs = 1 Sheet2.Range(“A1”).Value = “Order Number” Sheet2.Range(“B1”).Value = “Part Number” For Each rng In Sheet1.Range(“A:A”) If Trim(rng.Value) = “” Then End x = Split(rng.Text, “,”) For i = 1 To UBound(x) Sheet2.Range(“A1”).Offset(offs).Value = x(0) Sheet2.Range(“B1”).Offset(offs).Value = x(i) offs = offs + 1 Next i Next rng

Jerry Lundgren 16 Мар 2017 в 22:00

Источник

Как сделать цикл в vba по столбцу

Синтаксис цикла For… Next

Компоненты цикла For… Next

Примеры циклов For… Next

Простейший цикл

Простейший цикл с шагом

Цикл с отрицательными аргументами

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

Выход из цикла

Цикл с дробными аргументами

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

Цикл «For … Next»

Цикл «For Each»

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

Цикл «Do While» в Visual Basic

Цикл «Do Until» в Visual Basic

3 ответа

Похожие вопросы:

4 ответа