Excel копирование ячеек vba 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 новых строк. Учитывая такое количество формул - работа Книги будет очень медленной! Поэтому и нужно это все сделать через макрос.
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Аннотация
В этой статье обсуждаются многочисленные методы передачи данных в Microsoft Excel из приложения Microsoft Visual Basic. В этой статье также представлены преимущества и недостатки для каждого метода, чтобы вы могли выбрать решение, которое лучше всего работает для вас.
Дополнительные сведения
Наиболее распространенным методом для передачи данных в Excel является автоматизация. Автоматизация обеспечивает наибольшую гибкость для указания расположения данных в книге, а также возможности форматировать книгу и создавать различные параметры во время работы. С помощью автоматизации можно использовать несколько подходов для передачи данных:
- Перенос ячейки данных по ячейке
- Передача данных в массиве в диапазон ячеек
- Передача данных в наборе записей ADO в диапазон ячеек с помощью метода CopyFromRecordset
- Создание таблицы QueryTable на Excel, которая содержит результат запроса в источнике данных ODBC или OLEDB
- Передача данных в буфер обмена, а затем вклейка содержимого буфера обмена в Excel таблицу
Существуют также методы, которые можно использовать для передачи данных в Excel, которые не обязательно требуют автоматизации. Если вы работаете на сервере приложений, это может быть хорошим подходом для отвода основной части обработки данных от клиентов. Для передачи данных без автоматизации можно использовать следующие методы:
- Передача данных в текстовый файл с запятой или запятой, который можно Excel позже разбора в ячейки на таблице
- Передача данных на таблицу с помощью ADO
- Передача данных в Excel с помощью динамических Exchange данных (DDE)
В следующих разделах подробно по каждому из этих решений.
Примечание При использовании Microsoft Office Excel 2007 года можно использовать новый формат файла Excel 2007 (*.xlsx) при сохранения книг. Для этого найдите следующую строку кода в следующих примерах кода:
Замените этот код следующей строкой кода:
Кроме того, база данных Northwind не включена в Office 2007 г. по умолчанию. Однако вы можете скачать базу данных Northwind из Microsoft Office Online.
Использование автоматизации для передачи ячейки данных ячейкой
С помощью автоматизации можно передавать данные на одну ячейку одновременно:
Перенос ячейки данных по ячейкам может быть вполне приемлемым подходом, если объем данных невелик. Вы можете разместить данные в любой точке книги и можете условно отформатировать ячейки во время запуска. Однако этот подход не рекомендуется, если у вас есть большой объем данных для передачи в Excel книгу. Каждый объект Range, приобретаемый во время запуска, приводит к запросу интерфейса, чтобы перенос данных таким образом был медленным. Кроме того, microsoft Windows 95 и Windows 98 имеют ограничение в 64K для запросов интерфейса. Если вы достигнете или превысите это ограничение в 64k для запросов интерфейса, сервер автоматизации (Excel) может перестать отвечать или вы можете получить ошибки, указывающие на низкую память.
Еще раз, перенос ячейки данных по ячейкам приемлем только для небольших объемов данных. Если необходимо перенести большие наборы данных в Excel, следует рассмотреть одно из решений, представленных позже.
Дополнительные примеры кода для автоматизации Excel см. в Microsoft Excel Visual Basic.
Автоматизация для передачи массива данных в диапазон на таблице
Массив данных может быть передан сразу нескольким ячейкам:
При передаче данных с помощью массива, а не ячейки по ячейке можно реализовать огромный прирост производительности с большим количеством данных. Рассмотрим эту строку из кода выше, который передает данные в 300 ячеек в таблице:
Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, и другой для объекта Range, возвращаемого методом Resize). С другой стороны, для передачи ячейки данных по ячейке потребуются запросы на 300 интерфейсов в объекты Range. По возможности вы можете получать выгоду от передачи данных оптом и уменьшения количества запросов на интерфейс.
Автоматизация для передачи наборов записей ADO в диапазон таблиц
Excel 2000 г. представлен метод CopyFromRecordset, который позволяет переносить набор записей ADO (или DAO) в диапазон на таблице. В следующем коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 г. и передать содержимое таблицы заказов в базе данных образцов Northwind с помощью метода CopyFromRecordset.
Примечание Если вы используете Office 2007 года в базе данных Northwind, необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Excel 97 также предоставляет метод CopyFromRecordset, но использовать его можно только с набором записей DAO. CopyFromRecordset с Excel 97 не поддерживает ADO.
Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статьи How to transfer data from an ADO recordset to Excel с помощью автоматизации.
Автоматизация для создания queryTable на таблице
Объект QueryTable представляет таблицу, созданную из данных, возвращаемых из внешнего источника данных. При автоматизации Microsoft Excel можно создать queryTable, просто предоставив строку подключения к OLEDB или источнику данных ODBC вместе с SQL строкой. Excel берет на себя ответственность за создание наборов записей и вставку его в таблицу в заявляемом вами расположении. Использование QueryTables предоставляет ряд преимуществ по сравнению с методом CopyFromRecordset:
- Excel обрабатывает создание наборов записей и его размещение в таблицу.
- Запрос можно сохранить с помощью QueryTable, чтобы затем обновить его, чтобы получить обновленный набор записей.
- При добавлении в таблицу нового queryTable можно указать, что данные, уже существующие в ячейках на этом компьютере, будут перенесены для размещения новых данных (подробнее см. свойство RefreshStyle).
В следующем коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 г., чтобы создать новый queryTable в Excel таблице с использованием данных из базы данных образцов Northwind:
Использование буфера обмена
Буфер Windows также может использоваться в качестве механизма передачи данных на таблицу. Чтобы вклеить данные в несколько ячеек на таблицу, можно скопировать строку, в которой столбцы делимитированы символами вкладок, а строки делимитированы возвращаемой каретой. В следующем коде показано, как Visual Basic использовать объект Clipboard для передачи данных в Excel:
Создание разнонародных текстовых файлов, Excel можно разрезать на строки и столбцы
Excel могут открывать файлы с запятой или запятой и правильно разбора данных в ячейки. Вы можете воспользоваться этой функцией, если вы хотите передать большой объем данных на таблицу при использовании малого, если таково, автоматизации. Это может быть хорошим подходом для клиентского приложения-сервера, так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте, используя автоматизацию там, где это необходимо.
В следующем коде показано, как создать текстовый файл с запятой из наборов записей ADO:
Обратите внимание, что Office версии базы данных Northwind 2007 года необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Если в текстовом файле есть расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл запятой. Аналогично, если в вашем файле .TXT расширение, Excel автоматически разборите файл с помощью делимитеров вкладок.
В предыдущем примере кода Excel был запущен с помощью заявления Shell, а имя файла использовалось в качестве аргумента командной строки. Автоматизация не использовалась в предыдущем примере. Однако при желании можно использовать минимальное количество автоматизации для открытия текстового файла и сохранения его в формате Excel книги:
Передача данных на таблицу с помощью ADO
С помощью поставщика DB Microsoft Jet OLE можно добавить записи в таблицу в существующей Excel книге. "Таблица" в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заглавные (или имена полей), а все последующие строки содержат записи. Ниже показано, как создать книгу с пустой таблицей MyTable.
Excel 97, Excel 2000 и Excel 2003 г.
Запустите новую книгу в Excel.
Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:
A1: FirstName B1: LastName
Формат ячейки B1 в виде правой выровненной.
В меню Insert выберите Имена, а затем выберите Определение. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xls и Excel.
Чтобы добавить записи в MyTable с помощью ADO, можно использовать код, аналогичный следующему:
Excel 2007
В Excel 2007 г. запустите новую книгу.
Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:
A1: FirstName B1: LastName
Формат ячейки B1 в виде правой выровненной.
На ленте нажмите вкладку Формулы и нажмите кнопку Определить имя. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xlsx, а затем Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматированы с правильным выравниванием. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.
Следует отметить, что при добавлении записи в ячейку или ячейки в таблицу она переописывание любых данных, ранее в этих ячейках; Другими словами, строки в таблице не "сдвинуты" при добавлении новых записей. Это следует иметь в виду при разработке макета данных на ваших таблицах.
Метод обновления данных в Excel таблице с помощью ADO или с помощью DAO не работает в Visual Basic для среды приложения в access после установки Office 2003 Пакет обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью Microsoft Knowledge Base 904018. Метод хорошо работает в Visual Basic среде приложений из других Office приложений, таких как Word, Excel и Outlook.
Дополнительные сведения см. в следующей статье:
Дополнительные сведения об использовании ADO для доступа к книге Excel см. в книге How To Query and Update Excel Data Using ADO from ASP.
Использование DDE для передачи данных в Excel
DDE является альтернативой автоматизации в качестве средства для общения с Excel и передачи данных; однако с появлением автоматизации и com DDE больше не является предпочтительным методом для общения с другими приложениями и должен использоваться только в том случае, если у вас нет другого решения.
Чтобы передать данные в Excel DDE, вы можете использовать метод LinkPoke для отправки данных в определенный диапазон ячейки или метод LinkExecute для отправки команд, которые Excel будут выполняться.
В следующем примере кода показано, как установить беседу DDE с Excel, чтобы можно было втыкать данные в ячейки на таблицу и выполнять команды. С помощью этого примера для успешного запуска беседы по DDE в linkTopic Excel|MyBook.xls книга с именем MyBook.xls должна уже открываться в запущенном экземпляре Excel.
При использовании Excel 2007 можно использовать новый формат .xlsx для сохранения книг. Убедитесь, что имя файла обновляется в следующем примере кода. В этом примере Text1 представляет собой управление текстовым полем на Visual Basic форме:
При использовании LinkPoke с Excel, вы указываете диапазон в строке-столбце (R1C1) для LinkItem. Если вы подтыкаете данные к нескольким ячейкам, можно использовать строку, в которой столбцы делимитированы вкладками, а строки делимитированы возвращаемой каретой.
При использовании LinkExecute для Excel выполнения команды необходимо Excel команду в синтаксисе Excel макроса (XLM). Документация XLM не включается в Excel версии 97 и более поздней версии.
DDE не рекомендуется для общения с Excel. Автоматизация обеспечивает наибольшую гибкость и предоставляет дополнительный доступ к новым функциям, которые Excel предложить.
Функция записи макросов Excel используется не столько для создания хорошего кода, сколько для поиска названий необходимых объектов, методов и свойств. Например, при записи операции копирования и вставки можно получить код:
Sub Макрос()
Range( " A1 " ).Select
Selection.Copy
Range( " B1 " ).Select
ActiveSheet.Paste
End Sub
Обратите внимание, что данная программа выделяет ячейки. Однако в VBA для работы с объектом не обязательно его выделять. Данную процедуру можно заменить значительно более простой — применить метод Сору, который использует аргумент, представляющий адрес места вставки копируемого диапазона.
Sub CopyRange()
Range( " А1 " ).Copy Range( " В1 " )
End Sub
Предполагается, что рабочий лист является активным и операция выполняется на активном рабочем листе. Чтобы скопировать диапазон на другой рабочий лист или в другую книгу, необходимо задать ссылку:
Sub CopyRange2()
Workbooks( " File1.xlsx " ).Sheets( " Лист1 " ).Range( " A1 " ).Copy _
Workbooks( " File2.xlsx " ).Sheets( " Лист2 " ).Range( " A1 " )
End Sub
Еще одним подходом к решению этой задачи является использование для представления диапазонов объектных переменных:
Sub CopyRange3()
Dim Rngl As Range, Rng2 As Range
Set Rngl = Workbooks( " File1.xlsx " ).Sheets( " Лист1 " ).Range( " A1 " )
Set Rng2 = Workbooks( " File2.xlsx " ).Sheets( " Лист2 " ).Range( " A1 " )
Rngl.Copy Rng2 End Sub
Можно копировать большой диапазон. Адрес места вставки определяется единственной ячейкой (представляющей верхний левый угол вставляемого диапазона):
Sub CopyRange4 ()
Range( " А1:С800 " ).Copy Range( " D1 " )
End Sub
Для перемещения диапазона ячеек вместо метода Сору используется метод Cut.
Если размер копируемого диапазона не известен используется свойство CurrentRegion, возвращающее объект Range, который соответствует прямоугольнику ячеек вокруг заданной ячейки:
Sub CopyCurrentRegion2()
Range( " A1 " ).CurrentRegion.Copy Sheets( " Лист2 " ).Range( " A1 " )
End Sub
Метод End имеет один аргумент, определяющий направление, в котором увеличивается выделение ячеек. Следующий оператор выделяет диапазон от активной ячейки до последней непустой ячейки внизу:
Range (ActiveCell, ActiveCell.End(xlDown)).Select
Три остальные константы имитируют комбинации клавиш при выделении в других направлениях: xlUp (вверх), xlToLeft (влево) и xlToRight (вправо).
В прилагаемом Excel-файле определено несколько распространенных типов выделения ячеек (см. рис. 1). Код любопытен тем, что является также примером создания контекстного меню.
Запрос значения ячейки
Следующая процедура запрашивает значение у пользователя и вставляет его в ячейку А1:
Sub GetValuel()
Range( " A1 " ).Value = InputBox( " Введите значение " )
End Sub
Однако при выполнении этой процедуры возникает проблема. Если пользователь щелкнет на кнопке Отмена в окне ввода данных, то процедура удалит данные, которые находились в текущей ячейке. Модифицированная версия процедуры адекватно реагирует на щелчок на кнопке Отмена и не выполняет при этом никаких действий:
Sub GetValue2()
Dim UserEntry As Variant
UserEntry = InputBox( " Введите значение " )
If UserEntry <> " " Then Range( " A1 " ).Value = UserEntry
End Sub
Во многих случаях следует проверить правильность данных, введенных пользователем. Например, необходимо обеспечить введение только чисел в диапазоне от 1 до 12 (рис. 2). Это можно сделать при помощи процедуры GetValue3(), код которой приведен в Модуле1 приложенного Excel-файла. Некорректные данные игнорируются, и окно запроса значения отображается снова. Этот цикл будет повторяться, пока пользователь не введет правильное значение или не щелкнет на кнопке Отмена.
Рис. 2. Проверка данных, введенных пользователем
Ввод значения в следующую пустую ячейку
Если требуется ввести значение в следующую пустую ячейку столбца или строки, используйте код (рис. 3):
Sub GetData()
Dim NextRow As Long
Dim Entry1 As String, Entry2 As String
Do
' Определение следующей пустой строки
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
' Запрос данных
Entry1 = InputBox( " Введите имя " )
If Entry1 = " " Then Exit Sub
Entry2 = InputBox( " Введите сумму " )
If Entry2 = " " Then Exit Sub
' Запись данных
Cells(NextRow, 1) = Entry1
Cells(NextRow, 2) = Entry2
Loop
End Sub
Рис. 3. Макрос вставляет данные в следующую пустую строку рабочего листа
Это бесконечный цикл. Для выхода из него (щелкните на кнопке Cancel) использовались операторы Exit Sub. Обратите внимание строку, в который определяется значение переменной NextRow. Если вам трудно ее понять, проанализируйте содержимое ячейки: перейдите в последнюю ячейку столбца А и нажмите <End> и <↑>. После этого будет выделена последняя непустая ячейка в столбце А. Свойство Row возвращает номер этой строки; чтобы получить расположенную под ней строку (следующую пустую строку), к этому номеру прибавляется 1.
Приостановка работы макроса для определения диапазона пользователем
В некоторых ситуациях макрос должен взаимодействовать с пользователем. Например, можно создать макрос, который приостанавливается, когда пользователь указывает диапазон ячеек. Для этого воспользуйтесь функцией Excel InputBox. Не путайте метод Excel InputBox с функцией VBA InputBox. Несмотря на идентичность названий, это далеко не одно и то же.
Процедура, представленная ниже, демонстрирует, как приостановить макрос и разрешить пользователю выбрать ячейку. Затем автоматически формула вставляется в каждую ячейку выделенного диапазона.
Sub GetUserRange()
Dim UserRange As Range
Prompt = " Выберите диапазон для случайных чисел. "
Title = " Выбор диапазона "
' Отображение поля ввода
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) ' Выделение диапазона
On Error GoTo 0
' Отменено ли отображение поля ввода?
If UserRange Is Nothing Then
MsgBox " Отменено. "
Else
UserRange.Formula = " =RAND() "
End If
End Sub
Окно ввода данных показано на рис. 4. Важный момент в этой процедуре – определение аргумента Туре равным 8 (в этом случае InputBox вернет диапазон; подробнее см. Application.InputBox Method).
Рис. 4. Использование окна ввода данных с целью приостановки выполнения макроса
Обязательно проверьте, включено ли обновление экрана при использовании метода InputBox для выделения диапазона. Если обновление экрана отключено, вы не сможете выделить рабочий лист. Чтобы проконтролировать обновление экрана, в процессе выполнения макроса используйте свойство ScreenUpdating объекта Application.
Подсчет выделенных ячеек
Если активный лист содержит диапазон data, то следующий оператор присваивает количество ячеек в диапазоне data переменной с названием CellCount:
CellCount = Range( " data " ).Count
Вы можете также определить, сколько строк или столбцов содержится в диапазоне. Следующее выражение вычисляет количество столбцов в выделенном диапазоне:
Следующий оператор пересчитывает количество строк в диапазоне с названием data и присваивает это количество переменной RowCount.
RowCount = Range( " data " ).Rows.Count
Просмотр выделенного диапазона
Вы можете столкнуться с трудностями при создании макроса, который оценивает каждую ячейку в диапазоне и выполняет операцию, определенную заданному критерию. Если выделен целый столбец или строка, то работа макроса может занять много времени. Процедура ColorNegative устанавливает красный цвет для ячеек, которые содержат отрицательные значения. Цвет фона для других ячеек не определяется. Код процедуры можно найти в Модуле4 приложенного Excel-файла.
Усовершенствованная процедура ColorNegative2, создает объектную переменную WorkRange типа Range, которая представляет собой пересечение выделенного диапазона и диапазона рабочего листа (рис. 5). Если выделить столбец F (1048576 ячеек), то его пересечение с рабочим диапазоном В2:I16) даст область F2:F16, которая намного меньше исходного выделенного диапазона. Время, затрачиваемое на обработку 15 ячеек, намного меньше времени, уходящего на обработку миллиона ячеек.
Рис. 5. В результате пересечения используемого диапазона и выделенного диапазона рабочего листа уменьшается количество обрабатываемых ячеек
И всё же процедура ColorNegative2 недостаточно эффективна, поскольку обрабатывает все ячейки в диапазоне. Поэтому предлагается процедура ColorNegative3. В ней используется метод SpecialCells, с помощью которого генерируются два поднабора выделенной области: один поднабор (ConstantCells) включает ячейки, которые содержат исключительно числовые константы; второй поднабор (FormulaCells) включает ячейки, содержащие числовые формулы. Обработка ячеек в этих поднаборах осуществляется с помощью двух конструкций For Each-Next. Благодаря тому, что исключается обработка пустых и нетекстовых ячеек, скорость выполнения макроса существенно увеличивается.
Sub ColorNegative3()
' Окрашивание ячеек с отрицательными значениями в красный цвет
Dim FormulaCells As Range, ConstantCells As Range
Dim cell As Range
If TypeName(Selection) <> " Range " Then Exit Sub
Application.ScreenUpdating = False
' Создание поднаборов исходной выделенной области
On Error Resume Next
Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers)
Set ConstantCells = Selection.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
' Обработка ячеек с формулами
If Not FormulaCells Is Nothing Then
For Each cell In FormulaCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
' Обработка ячеек с константами
If Not ConstantCells Is Nothing Then
For Each cell In ConstantCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
End Sub
Оператор On Error необходим, поскольку метод SpecialCells генерирует ошибку, если не находит в диапазоне ячеек указанного типа.
Удаление всех пустых строк
Следующая процедура удаляет все пустые строки в активном рабочем листе. Она достаточно эффективна, так как не проверяет все без исключения строки, а просматривает только строки в так называемом «используемом диапазоне», определяемом с помощью свойства UsedRange объекта Worksheet.
Первый шаг — определить последнюю используемую строку и присвоить этот номер строки переменной LastRow. Это не так просто, как можно ожидать, поскольку текущий диапазон необязательно начинается со строки 1. Следовательно, значение LastRow вычисляется таким образом: к найденному количеству строк используемого диапазона прибавляется номер первой строки текущего диапазона и вычитается 1.
В процедуре применена функция Excel СЧЁТЗ, определяющая, является ли строка пустой. Если данная функция для конкретной строки возвращает 0, то эта строка пустая. Обратите внимание, что процедура просматривает строки снизу вверх и использует отрицательное значение шага в цикле For-Next. Это необходимо, поскольку при удалении все последующие строки перемещаются «вверх» в рабочем листе. Если бы в цикле просмотр выполнялся сверху вниз, то значение счетчика цикла после удаления строки оказалось бы неправильным.
Дублирование строк
Пример, рассматриваемый в этом разделе, демонстрирует использование возможностей VBA для создания дубликатов строк. На рис. 6 показан пример рабочего листа, используемого организаторами лотереи. В столбце А вводится имя. В столбце В содержится количество лотерейных билетов, приобретенных одним покупателем. В столбце С находится случайное число сгенерированное с помощью функции СЛЧИС. Победитель определяется путем сортировки данных в третьем столбце (выигрыш соответствует наибольшему случайному числу).
Рис. 6. Дублирование строк на основе значений в столбце В
А теперь нужно продублировать строки, в результате чего количество строк для каждого участника лотереи будут соответствовать количеству купленных им билетов. Например, если Барбара приобрела два билета, для нее создаются две строки. Ниже показана процедура, выполняющая вставку новых строк.
Объектная переменная cell была инициализирована ячейкой В2, первой ячейкой, в которой находится числовая величина. Вставка новых строк осуществляется в цикле, а их копирование происходит с помощью метода FillDown. Значение переменной cell увеличивается на единицу, после чего выбирается следующий участник лотереи, Цикл выполняется до тех пор, пока не встретится пустая ячейка. На рис. 7 показан рабочий лист после выполнения этой процедуры.
Рис. 7. В соответствии со значением в столбце В добавлены новые строки
Определение диапазона, находящегося в другом диапазоне
Функция InRange имеет два аргумента, оба — объекты Range. Функция возвращает значение True (Истина), если первый диапазон содержится во втором.
Возможно, функция InRange кажется сложнее, чем того требует ситуация, поскольку в коде должна быть реализована проверка принадлежности двух диапазонов одной и той же книге и рабочему листу. Обратите внимание, что в процедуре используется свойство Parent, которое возвращает объект-контейнер заданного объекта. Например, следующее выражение возвращает название листа для объекта rng1:
Следующее выражение возвращает название рабочей книги rng1:
Функция VBA Union возвращает объект Range, который представляет собой объединение двух объектов типа Range. Объединение содержит все ячейки, относящиеся к исходным диапазонам. Если адрес объединения двух диапазонов совпадает с адресом второго диапазона, первый диапазон входит в состав второго диапазона.
Определение типа данных ячейки
В состав Excel входит ряд встроенных функций, которые могут помочь определить тип данных, содержащихся в ячейке. Это функции ЕНЕТЕКСТ, ЕЛОГИЧ и ЕОШИБКА. Кроме того, VBA поддерживает функции IsEmpty, IsDate и IsNumeric.
Ниже описана функция CellType, которая принимает аргумент-диапазон и возвращает строку, описывающую тип данных левой верхней ячейки этого диапазона (рис. 8). Такую функцию можно использовать в формуле рабочего листа или вызвать из другой процедуры VBA.
Рис. 8. Функция CellType, возвращающая тип данных ячейки
Function CellType(Rng)
' Возвращает тип ячейки, находящейся в левом верхнем углу диапазона
Dim TheCell As Range
Set TheCell = Rng.Range( " A1 " )
Select Case True
Case IsEmpty(TheCell)
CellType = " Пустая "
Case TheCell.NumberFormat = " @ "
CellType = " Текст "
Case Application.IsText(TheCell)
CellType = " Текст "
Case Application.IsLogical(TheCell)
CellType = " Логический "
Case Application.IsErr(TheCell)
CellType = " Ошибка "
Case IsDate(TheCell)
CellType = " Дата "
Case InStr(1, TheCell.Text, " : " ) <> 0
CellType = " Время "
Case IsNumeric(TheCell)
CellType = " Число "
End Select
End Function
Обратите внимание на использование оператора SetTheCell. Функция CellType получает аргумент-диапазон произвольного размера, но этот оператор указывает, что функция оперирует только левой верхней ячейкой диапазона (представленной переменной TheCell).
Подобно функции листа в Excel, мы также можем копировать данные вставки или набор значений в VBA. В обычных данных мы используем CTRL + C, чтобы скопировать выборку данных, а затем CTRL + V, чтобы вставить выбранные данные в целевую ячейку. Но то же самое в VBA делают определенные коды, которые мы узнаем в этой статье.
Как мы используем копирование и вставку в Excel VBA? Для этого сначала нам нужно активировать целевой лист или рабочую книгу, из которой мы хотим скопировать любой диапазон данных, и этот выбор копируется определенным кодом, когда этот выбор успешно скопирован, мы можем продолжить и вставить данные в целевой диапазон ячеек.
Синтаксис для копирования данных в VBA выглядит следующим образом:
Теперь синтаксис для вставки данных в другой лист или в любой другой диапазон ячеек выглядит следующим образом:
Мы используем оператор Dot (.) Для использования методов копирования и вставки в VBA.
Мы можем скопировать весь столбец и вставить его в другой столбец, а также мы также можем скопировать всю строку и вставить ее в другую строку. Мы узнаем все это в этой статье.
Примечание. Чтобы использовать VBA в Excel, убедитесь, что вкладка «Разработчик» включена из вкладки «Файл», а затем в раздел настроек параметров.
Как использовать Excel VBA Copy Paste?
Мы научимся использовать VBA Copy Paste с несколькими примерами в Excel.
Вы можете скачать этот шаблон VBA Copy Paste Excel здесь - Шаблон VBA Copy Paste Excel
VBA Copy Paste - Пример № 1
Для демонстрации у меня есть случайное значение в ячейке A1, которое я хочу скопировать и вставить в ячейку B1, используя код VBA. Посмотрите, какие данные находятся в ячейке A1, а ячейка B1 пуста.
Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:
Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.
Шаг 2: Как только редактор VB откроется, нажмите на кнопку вставки, а затем на кнопку Вставить модуль, чтобы вставить окно кода.
Шаг 3: Объявите подфункцию, чтобы начать писать код.
Код:
Шаг 4. Сначала активируйте лист, чтобы использовать свойства листа с помощью следующего кода.
Код:
Шаг 5: Скопируйте данные, которые находятся в ячейке A1, с помощью следующего кода.
Код:
Шаг 6: Теперь вставьте скопированные данные в целевую ячейку, которая является ячейкой B1, с помощью следующего кода.
Код:
Шаг 7: Запустите приведенный выше код с помощью кнопки запуска и посмотрите результат в ячейке B1.
VBA Copy Paste - Пример №2
У меня есть данные в столбце C, и я хочу скопировать все данные или значения и вставить их в столбец D, используя код VBA. Посмотрите ниже, что это за данные в столбце C, и что столбец D пуст.
Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:
Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.
Шаг 2: Нажмите на вставленный модуль, чтобы открыть окно кода,
Шаг 3: Объявите подфункцию, чтобы начать писать код.
Код:
Шаг 4. Сначала активируйте рабочий лист с помощью следующего кода.
Код:
Шаг 5: Скопируйте данные в столбце C с помощью следующего кода.
Код:
Шаг 6: Теперь, чтобы вставить данные в столбец D, используйте следующий код.
Код:
Шаг 7: Запустите следующий код с помощью кнопки запуска или нажмите F5.
Запустите код, чтобы увидеть следующий результат.
VBA Copy Paste - Пример № 3
Теперь для этого примера у меня есть целый диапазон данных в диапазоне ячеек G1: H3, и я хочу скопировать данные в диапазоне ячеек I1: J3. Посмотрите ниже на данные, которые у меня есть в диапазоне ячеек G1: H3, а диапазон ячеек I1: J3 пуст.
Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:
Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.
Шаг 2: Нажмите на вставленный модуль, чтобы открыть окно кода,
Шаг 3: Объявите подфункцию, чтобы начать писать код.
Код:
Шаг 4. Сначала активируйте лист, чтобы использовать его свойства с помощью следующего кода.
Код:
Шаг 5: Скопируйте данные в целевой диапазон ячеек с помощью следующего кода.
Код:
Шаг 6: Теперь, чтобы вставить данные в ячейку назначения, используйте следующий код.
Код:
Шаг 7: Запустите приведенный выше код с помощью кнопки запуска или нажмите F5, чтобы увидеть следующий результат.
VBA Copy Paste - пример № 4
Для этого примера у меня есть данные в строке 10, и я хочу вставить данные в строке 11. Посмотрите ниже, чтобы увидеть, какие данные находятся в строке 10, а строка 11 свободна.
Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:
Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.
Шаг 2: Нажмите на вставленный модуль, чтобы открыть окно кода,
Шаг 3: Объявите подфункцию, чтобы начать писать код.
Код:
Шаг 4. Активируйте рабочий лист, чтобы использовать свойства рабочего листа.
Код:
Шаг 5: Скопируйте строку 10 со следующим кодом.
Код:
Шаг 6: Вставьте данные строки 10 в строку 11 с помощью следующего кода.
Код:
Шаг 7: Запустите приведенный выше код, нажав F5, чтобы увидеть следующий результат.
То, что нужно запомнить
- Чтобы использовать данные из любого листа для их копирования, нам нужно сначала активировать лист.
- Точно так же, когда нам нужно вставить данные в любой другой лист в VBA, нам нужно сначала активировать целевой лист.
- Если мы копируем весь столбец или строку и вставляем данные в любой другой столбец, тогда данные в любом месте строки или столбца в целевых ячейках копируются и вставляются в целевые ячейки. Это может привести к определенным нежелательным данным.
- Лучший способ скопировать данные - скопировать определенный диапазон и вставить данные в целевые ячейки.
Рекомендуемые статьи
Это было руководство к VBA Copy Paste. Здесь мы обсудили, как использовать Excel VBA Copy paste, а также некоторые практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: