Vba excel перенос строки по условию

Обновлено: 04.07.2024

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

В процессе написания кода, программист может иметь совершенно чёткое представление о том, что за код он пишет и как этот код должен работать. Но нужно позаботиться и о том, чтобы, вернувшись к работе спустя полгода, не пришлось ломать голову, пытаясь понять, что должен делать этот код. Ещё более неприятная ситуация – когда кто-то другой станет продолжать Вашу работу над кодом и не сможет понять, как он работает.

Эта статья посвящена комментариям, отступам в коде и переносам строк – элементам, которые делают код аккуратным и понятным.

Комментарии в VBA

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

Ниже продемонстрировано, как при помощи комментариев поясняется работа простой процедуры Sub:

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

Часто программисты ленятся добавлять подробные комментарии к своему коду, но, поверьте, затраченные усилия оправдают себя с избытком! Несколько минут, потраченных на написание понятного комментария, могут сэкономить Вам долгие часы в будущем.

Отступы в коде VBA

Другой приём, делающий написанный код более читаемым – правильно расставлять отступы. В приведённом выше примере видно, что отступ сделан для кода внутри главной процедуры Sub и далее отступ увеличивается для каждого вложенного блока кода. Такие увеличенные отступы помогают понять, где каждый отдельный блок кода начинается и заканчивается.

Переносы строк в VBA

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

Посмотрите на этот оператор If:

При помощи переносов строк тот же оператор If может быть записан вот так:

Если рассмотренный оператор If разбит на четыре строки, то составляющие его блоки c условиями видны гораздо более наглядно. Этот пример иллюстрирует, как аккуратное оформление может сделать код более читаемым и привести в результате к меньшему количеству ошибок и путаницы.

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

Допустим, что у нас имеется база данных по клиентам, где одному названию компании может соответствовать несколько разных email'ов ее сотрудников. Наша задача состоит в том, чтобы собрать все адреса по названиям компаний и сцепить их (через запятую или точку с запятой), чтобы сделать потом, например, почтовую рассылку по клиентам, т.е. получить на выходе что-то похожее на:

Другими словами, нам нужен инструмент, который будет склеивать (сцеплять) текст по условию - аналог функции СУММЕСЛИ (SUMIF) , но для текста.

Способ 0. Формулой

Не очень изящный, зато самый простой способ. Можно написать несложную формулу, которая будет проверять отличается ли компания в очередной строке от предыдущей. Если не отличается, то приклеиваем через запятую очередной адрес. Если отличается, то "сбрасываем" накопленное, начиная заново:

Минусы такого подхода очевидны: из всех ячеек полученного дополнительного столбца нам нужны только последние по каждой компании (желтые). Если список большой, то чтобы их быстро отобрать придется добавить еще один столбец, использующий функцию ДЛСТР (LEN) , проверяющий длину накопленных строк:

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

Способ 1. Макрофункция склейки по одному условию

Если исходный список не отсортирован по компаниям, то приведенная выше простая формула не работает, но можно легко выкрутиться с помощью небольшой пользовательской функции на VBA. Откройте редактор Visual Basic нажатием на сочетание клавиш Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) . В открывшемся окне вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст нашей функции:

Если теперь вернуться в Microsoft Excel, то в списке функций (кнопка fx в строке формул или вкладка Формулы - Вставить функцию) можно будет найти нашу функцию MergeIf в категории Определенные пользователем (User Defined) . Аргументы у функции следующие:

Способ 2. Сцепить текст по неточному условию

Если заменить в 13-й строчке нашего макроса первый знак = на оператор приблизительного совпадения Like, то можно будет осуществлять склейку по неточному совпадению исходных данных с критерием отбора. Например, если название компании может быть записано в разных вариантах, то мы можем одной функцией проверить и собрать их все:

Поддерживаются стандартные спецсимволы подстановки:

По умолчанию оператор Like регистрочувствительный, т.е. понимает, например, "Орион" и "оРиОн" как разные компании. Чтобы не учитывать регистр можно добавить в самое начало модуля в редакторе Visual Basic строчку Option Compare Text, которая переключит Like в режим, когда он невосприимчив к регистру.

Таким образом можно составлять весьма сложные маски для проверки условий, например:

Способ 3. Макрофункция склейки текста по двум условиям

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

Применяться она будет совершенно аналогично - только аргументов теперь нужно указывать больше:

Способ 4. Группировка и склейка в Power Query

Решить проблему можно и без программирования на VBA, если использовать бесплатную надстройку Power Query. Для Excel 2010-2013 ее можно скачать здесь, а в Excel 2016 она уже встроена по умолчанию. Последовательность действий будет следующей:

Power Query не умеет работать с обычными таблицами, поэтому первым шагом превратим нашу таблицу в "умную". Для этого ее нужно выделить и нажать сочетание Ctrl + T или выбрать на вкладке Главная - Форматировать как таблицу (Home - Format as Table) . На появившейся затем вкладке Конструктор (Design) можно задать имя таблицы (я оставил стандартное Таблица1):

Теперь загрузим нашу таблицу в надстройку Power Query. Для этого на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013) жмем Из таблицы (Data - From Table) :

В открывшемся окне редактора запросов выделяем щелчком по заголовку столбец Компания и сверху жмем кнопку Группировать (Group By) . Вводим имя нового столбца и тип операции в группировке - Все строки (All Rows) :

Жмем ОК и получаем для каждой компании мини-таблицу сгруппированных значений. Содержимое таблиц хорошо видно, если щелкать левой кнопкой мыши в белый фон ячеек (не в текст!) в получившемся столбце:

Теперь добавим еще один столбец, где с помощью функции склеим через запятую содержимое столбцов Адрес в каждой из мини-таблиц. Для этого на вкладке Добавить столбец жмем Пользовательский столбец (Add column - Custom column) и в появившемся окне вводим имя нового столбца и формулу сцепки на встроенном в Power Query языке М:

Обратите внимание, что все М-функции регистрочувствительные (в отличие от Excel). После нажатия на ОК получаем новый столбец со склееными адресами:

Осталось удалить ненужный уже столбец ТаблАдресов (правой кнопкой мыши по заголовку - Удалить столбец) и выгрузить результаты на лист, нажав на вкладке Главная - Закрыть и загрузить (Home - Close and load) :

Важный нюанс : в отличие от предыдущих способов (функций), таблицы из Power Query не обновляются автоматически. Если в будущем произойдут какие-либо изменения в исходных данных, то нужно будет щелкнуть правой кнопкой в любое место таблицы результатов и выбрать команду Обновить (Refresh) .

Удаление дублирующих строк с условием
Доброй ночи. необходимо взять 3 столбца (1- номера заказов, 2 - сборщики, 3 - количество коробов).

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

Как программно удалить несколько строк на листе excel, связанных между собой определенным условием?
Как программно удалить несколько строк на листе excel связанных между собой определенным условием?

перенос строк
Добрый день, нужен макрос, или как по другому решить: Нужно скопировать строку на другой лист.

Пример таблицы во вложениях

Прошу по возможности помочь реализовать

Добавлено через 16 часов 16 минут
Нашел скрипт, который выполняет необходимую мне функцию, но при каждом запуске копирует все строки, в том числе те, которые уже скопировал ранее

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

Добавить новую строку?

Lofak, ну, конечно, перед копированием поставить чистку. Только надо активировать страницу , на которую пойдёт запись, либо проще ws.rows.delete ведь ws у вас лист, в который идет перенос? Ну и его надо поставить до цикла по I Только надо активировать страницу , на которую пойдёт запись, либо проще ws.rows.delete ведь ws у вас лист, в который идет перенос? Ну и его надо поставить до цикла по I

Ну и его надо поставить до цикла по I
А на то, что вам пишут вы внимания не обращаете. А цикл по по строкам у вас в строке 6

Добавлено через 23 минуты
Lofak, под ЕГО имеется ввиду оператор чистки, а вы засунули его внутрь цикла .

Lofak, под ЕГО имеется ввиду оператор чистки, а вы засунули его внутрь цикла .

К сожалению не пойму, перед?

Lofak, НУ ЭТО УЖ КУ-КУ. Перед это не внутрь строки кода, а перед ней. Как мне кажется, перенос осуществляется не AWS а WS
Перед это не внутрь строки кода, а перед ней. Как мне кажется, перенос осуществляется не AWS а WS Да, я так и имел ввиду, но в таком случае выпадает ошибка, что переменная не задана:

Решение

Lofak, я вам написал ответ на вопрос о чистке листа. В вашем коде до оператора чистки надо установить значение объекта set WS = какой-то лист.
Либо активировать НУЖНЫЙЛИСТ.ACTIVATE , потом Rows.delete и потом снова перейти на лист AWS и далее цикл

Добавлено через 5 минут
Lofak, либо вычистите все листы до того как переносить данные из основной базы

Добавлено через 5 минут
например так

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

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

Перенос найденных строк в VBA
Подскажите как перенести строчку с одного листа на другой, которую я нахожу через .find Sub.

Не работает перенос строк в InputBox :-[
Всех приветствую! Чтобы вывести текст в несколько строк для InputBox'a MsgBox'a, всегда делал так.

Перенос данных из строк в столбец
Господа, добрый день. Использую макрос для переноса данных из столбцов в один столбец.

Всем доброго времени суток!

Имеется 6 листов: Export, Import, Local, Closed Export, Closed Local, Closed Import

Как мне при условии, если в последней колонке будет написано "Не нужно" - перенести эту строку в Closed.

Например в Local пишем в конце строчки "Не нужно" и она переносится в конец Closed local.

Всем доброго времени суток!

Имеется 6 листов: Export, Import, Local, Closed Export, Closed Local, Closed Import

Как мне при условии, если в последней колонке будет написано "Не нужно" - перенести эту строку в Closed.

Например в Local пишем в конце строчки "Не нужно" и она переносится в конец Closed local. Oh_Nick

Имеется 6 листов: Export, Import, Local, Closed Export, Closed Local, Closed Import

Как мне при условии, если в последней колонке будет написано "Не нужно" - перенести эту строку в Closed.

Например в Local пишем в конце строчки "Не нужно" и она переносится в конец Closed local. Автор - Oh_Nick
Дата добавления - 02.03.2021 в 18:14

Oh_Nick, проблема в том, что слово "переносится" можно понимать по разному. Для одних это - скопировать и вставить, для других - вырезать и вставить. Посмотрите вариант с копированием, при необходимости замену копирования на вырезание доделаете сами. В модули листов Export, Import и Local:
[vba] Oh_Nick, проблема в том, что слово "переносится" можно понимать по разному. Для одних это - скопировать и вставить, для других - вырезать и вставить. Посмотрите вариант с копированием, при необходимости замену копирования на вырезание доделаете сами. В модули листов Export, Import и Local:
[vba] [/vba] Автор - _Igor_61
Дата добавления - 02.03.2021 в 23:47

_Igor_61, спасибо. Немного модернизировал:

On Error GoTo 0
Er1:
Exit Sub
End Sub

Привязал к каждому листу и колонке.

_Igor_61, спасибо. Немного модернизировал:

On Error GoTo 0
Er1:
Exit Sub
End Sub

Привязал к каждому листу и колонке. Oh_Nick

On Error GoTo 0
Er1:
Exit Sub
End Sub

Привязал к каждому листу и колонке. Автор - Oh_Nick
Дата добавления - 03.03.2021 в 09:26

Читайте также: