Excel макрос копирование ячейки по условию
Обновлено: 06.07.2024
Столкнулся с непосильной задачей. Знания в написании макросов на уровне новичка, да и то, в большей степени по примерам, так что прошу помощи. Буду благодарен за любые советы.
И так, есть некий (см. вложение) в котором на первом листе заполняются данные каждый день. Хотелось бы чтобы данные с первого листа копировались на второй лист по условию: а именно на первом листе в 1ом столбце числа. Нужно копировать данные на второй лист по числам. Если второе число не вводится то по умолчанию считаем, что копирование происходит с введенного числа до конца заполненных ячеек. Копировать необходимо только с 3 по 7 столбец Листа1. С последующей выгрузкой на отправку на почту с названием которое берется с ячейки R1 лист1. Внутри два макроса. Помогите довести макрос до ума.
ЗЫ. Второй лист можно и изключить. Он у меня как буфер для отправки почты
Помощь в написании контрольных, курсовых и дипломных работ здесь
Макрос копирования ячеек по условию в Excel
Здравствуйте господа программисты! Столкнулся с непосильной задачей. Знания в написании макросов.
Макрос копирования по условию
Добрый день форумчане, нужен макрос для копирования строк с определенным значением в столбце с.
Макрос копирования таблицы и текста из ячеек
Добрый день. У меня вот такой вопрос. Есть книга , а в ней 150 листов. Эта книга – большая.
Макрос копирования ячеек со сложным условием
Пытался поменять тему в прошлой теме, так как она не отражает всего действия. Делаю форму чтобы.
Покажите, что должно получиться на 2-ом листе( как пример) На втором листе должно получится столбцы первого листа с 3го по 7ой. определенной даты в примере есть макрос. который я нарыл в сети, но не знаю как приспособить его под свою задачу. Макрос почта работает как полагается. Идеальный вариант бы их объеденить. И первую часть с разъяснением. так как пока далек от детального понимания да по копированию это то что надо, в плане столбцов. Но копирует полностью массив, а необходимо чтоб копировал только массив определенного числа. Иначе говоря должен в столбце А найти необходимое число. Положем оно находится на 15ой строке. Значит верхнюю точку массива должен присвоить не Cells(3, 3) а Cells(15, 3), далее найти наше число +1 ниже. И поставить ограничение нижнего угла массива Cells(28, 7). А если нашего числа+1 нет, то ограничится пустой ячейкой. да по копированию это то что надо, в плане столбцов. Но копирует полностью массив, а необходимо чтоб копировал только массив определенного
Я же просил . показать на втором листе.
да по копированию это то что надо, в плане столбцов. Но копирует полностью массив, а необходимо чтоб копировал только массив определенного числа. Иначе говоря должен в столбце А найти необходимое число. Положем оно находится на 15ой строке. Значит верхнюю точку массива должен присвоить не Cells(3, 3) а Cells(15, 3), далее найти наше число +1 ниже. И поставить ограничение нижнего угла массива Cells(28, 7). А если нашего числа+1 нет, то ограничится пустой ячейкой.Необходимо выделить значения определенной даты. - подтвердите, правильно ли я понял?
Добавлено через 3 минуты
Вечером дома посмотрю.
если число 28.11.2017 то соответственно массив (с42:i) где i = до свободной ячейки в столбце D. И для копирования был запрос на ввод даты. Типа:
dat1 = CDate(InputBox("Введите начало периода"))
Макрос как я понимаю должен быть что то подобие этого:
второе: макрос копирует не значения а формулы, а надо чтоб копировал формат ячейки и данные
ну и третье: как вписать копирование шапки таблицы как константу. с первого листа массива C1:G2
Добавлено через 1 час 34 минуты
После доработки пришел к виду, что я и хотел
Не могу додумать как же вставить ввод числа (как пример dat1 = CDate(InputBox("Введите начало периода"))), пробывал вставлять строку, и тогда начинается нескончаемый цикл с вводом числа. иначе с=говоря строка уходит в цикл
И не могу додумать как же все же на втором листе получить не формулы а значения в скопированных ячейках.
Добавлено через 3 минуты
ЗЫ. at1 = CDate(InputBox("Введите начало периода"))) - уже приделал. пустил 1ой строкой. и ввод даты перестал входить в цикл
Посмотрите прикрепленный файл.
Двойной щелчок по ячейке "D1" (где написано "Время")
Вместо Inputbox'a использовал фому.
Вводите дату в формате "ДД/ММ/ГГГГ"
Не учтены ошибки при вводе дат, таких как 31/02/2018 или 31/06/2017
Если что не понятно, пишите.
P.S. Простите за задержку, на работе -аврал.
Все просто замечательно, по этой задаче. Даже через форму сделано - это просто супер, но мы же простых путей не ищем. С кодом вроде все понятно так же. Но вот теперь еще другой вопрос: а если усложнить задачу таким образом, чтоб лист два был не физичиский лист, а переменный (иначе говоря виртуальный - как буфер обмена) чтоб происходило бы открытие нового файла, куда вставлялось бы, все то что скопировано и уходило по почте. (это вторая часть изночального соощения) мой код подобной работы выглядит так:Добрый день. Не стал создавать отдельную тему, т.к моя проблема очень похожа на описанную здесь, но более сложная.
У меня есть Книга с 3 листами. Лист "СВОД" заполняется каждый день. Мне надо, чтоб в зависимости от того, какой поставщик указан (столбец С), данные копировались на соответствующий лист.
Важно. 1) В листах поставщиков ("RP" & "LK") данные не должны перезаписывать предыдущие уже существующие, а должны добавляться ниже.
2) Лист "RP" - колонка Н - это колонка I Лист "СВОД" делить на 1000
3) Лист "LK" - колонка Н - это колонка I Лист "СВОД" делить на 1000
Как я вижу должен работать макрос.
Я заполняю Лист "СВОД". Затем вызываю макрос, указываю дату (Лист "СВОД" колонка В). Соответствующие данные копируются на свои листы.
Очень прошу мне помочь с макросом в решении моей проблемы. Спасибо!
P.S почему мне не подходит вариант это все сделать через формулы и ссылки. Потому что ежедневно добавляется до 100 новых строк. Учитывая такое количество формул - работа Книги будет очень медленной! Поэтому и нужно это все сделать через макрос.
Про то, как можно быстро склеивать текст из нескольких ячеек в одну и, наоборот, разбирать длинную текстовую строку на составляющие я уже писал. Теперь же давайте рассмотрим близкую, но чуть более сложную задачу - как склеивать текст из нескольких ячеек при выполнении определенного заданного условия.
Допустим, что у нас имеется база данных по клиентам, где одному названию компании может соответствовать несколько разных 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) .
На одном листе расположен список повторяющихся городов с информацией о предприятиях общепита:
Исходная таблица задания №1
Необходимо данные по каждому городу перенести в одну строку на другом листе (таблица обрезана справа):
Часть результирующего списка задания №1
Решение копированием с листа на лист
Это решение значительно проще, чем с использованием массивов, но более медленное. При больших объемах информации обработка может длиться достаточно долго. Решение достигается путем присваивания значений ячеек из таблицы первого листа ячейкам второго листа.
Dim n1 As Long , n2 As Long , n3 As Long , n4 As Long , _ n1 = Sheets ( "Лист1" ) . Cells ( 1 , 1 ) . CurrentRegion . Rows . CountПеременные:
- n1 – количество строк в исходной таблице;
- n2 – номер столбца текущей ячейки исходной таблицы, к которой обращается цикл;
- n3 – номер строки текущей ячейки на втором листе;
- n4 – номер столбца текущей ячейки на втором листе;
- i1 – счетчик цикла For… Next;
- gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.
Решение с использованием массивов
Циклы в массивах работают очень быстро, но решение с ними может быть сложнее, чем при простом присваивании значений одних ячеек другим. В этом примере для упрощения кода две процедуры записаны отдельно и в нужные моменты вызываются для исполнения.
Подпрограммы Kopirovanie и Vstavka используются в цикле For. Next процедуры Resheniye2 по два раза, поэтому их коды вынесены за пределы процедуры Resheniye2 и вызываются по мере необходимости.
Переменные:
- massiv1 – его элементам присваиваются значения ячеек исходной таблицы;
- massiv2 – одномерный массив, заполняемый данными из переменной txt1;
- massiv3 – двумерный массив, заполняемый данными из одномерного массива massiv2 и используемый для вставки очередной строки на второй лист;
- txt1 – сюда копируются через разделитель значения элементов массива massiv1, предназначенные для заполнения очередной строки на втором листе;
- n1 – количество строк в исходной таблице;
- n2 – количество столбцов в исходной таблице;
- n3 – номер текущей строки на втором листе;
- n4 – количество столбцов текущей строки на втором листе (соответствует количеству элементов массива massiv2);
- i1, i2, i3 – счетчики цикла For… Next;
- gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.
Переменные, использующиеся более чем в одной процедуре, объявлены как глобальные в разделе Declarations программного модуля.
Читайте также: