Копировать строк найденных в поиске excel vba
Поиск и замена выставлены объектами Поиска и Замены . Объект Find доступен в объекте Selection и объекте Range . Действие поиска немного отличается в зависимости от того, имеется ли доступ к объекту Find из объекта Selection или объекта Range .
Поиск текста и его выбор
Если объект Find имеет доступ к объекту Selection , выбор меняется при обнаружении критериев поиска. В следующем примере выбирается следующее появление слова "Hello". Если конец документа достигнут до того, как будет найдено слово "Hello", поиск будет остановлен.
Объект Find содержит свойства, которые относятся к параметрам в диалоговом окне Найти и заменить. Можно установить отдельные свойства объекта Find или использовать аргументы с методом Execute , как показано в следующем примере.
Поиск текста без изменения выбора
Если объект Find имеет доступ к объекту Range, выбор не меняется, но диапазон переопределяется при обнаружении критериев поиска. В следующем примере обнаруживается первое появление слова "синий" в активном документе. Если операция поиска будет успешной, диапазон будет переопределяться и к слову "синий" применяется смелый форматирование.
В следующем примере выполняется тот же результат, что и в предыдущем примере, с использованием аргументов метода Execute .
Использование объекта Replacement
Объект Replacement представляет критерии замены для операции поиска и замены. Свойства и методы объекта Replacement соответствуют параметрам в диалоговом окне Найти и Заменить (Изменить меню).
Объект Replacement доступен в объекте Find . В следующем примере все вхождения слова "привет" заменяется на "hello". Выбор изменяется при обнаружении критериев поиска, так как объект Find имеет доступ к объекту Selection .
В следующем примере удаляется жирный форматирование в активном документе. Свойство Bold — true для объекта Find и False для объекта Replacement . Чтобы найти и заменить форматирование, установите поиск и замените текст пустыми строками ("") и установите аргумент Format метода Execute true. Выбор остается неизменным, так как объект Find имеет доступ к объекту Range (свойство Content возвращает объект Range ).
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.
В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow. Объявлять мы её будем как Long . Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767(это максимальное допустимое значение переменных типа Integer ) нам понадобиться именно Long , во избежание ошибки. Подробнее про типы переменных можно прочитать в статье Что такое переменная и как правильно её объявить
Одинаковые переменные для всех примеров
Dim lLastRow As Long 'а для lLastCol можно применить тип Integer, 'т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long
определяя таким способом нам надо знать что:
1 - это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
Это самый распространенный метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.
Правда, следует знать одну вещь: если у вас заполнены все строки в просматриваемом столбце(или будет заполнена самая последняя ячейка столбца) - то результат будет неверный(ну или не совсем такой, какой ожидали увидеть вы)
Определение последнего столбца через свойство End
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
1 - это номер строки, последнюю заполненную ячейку в которой мы определяем.
Данный метод лишен недостатков, присущих второму и третьему способам. Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой Скрыть (Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.
Определение последнего столбца через SpecialCells
Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку(строку - Row либо столбец - Column). Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Если вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то она тоже будет считаться заполненной.
Плюс данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование -Защитить лист).
Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.
Способ 3:
Определение последней строки через UsedRange
Определение последнего столбца через UsedRange
Если хотите получить первую пустую ячейку на листе придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая - следующая за ней. Т.е. к результату необходимо прибавить 1.
Способ 4:
Определение последней строки и столбца, а так же адрес ячейки методом Find
Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул - только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими пустую ячейку(=""), предыдущие варианты вернут строку/столбец ячейки с последней формулой, в то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки и не тратить время на их проверку.
Однако данный метод не будет учитывать в просмотре скрытые строки и столбцы. Это следует учитывать при его применении.
небольшой практический код , который поможет вам понять, как использовать полученную переменную:
Sub Get_Last_Cell() Dim lLastRow As Long Dim lLastCol As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row MsgBox "Заполненные ячейки в столбце А: " & Range("A1:A" & lLastRow).Address lLastCol = Cells.SpecialCells(xlLastCell).Column MsgBox "Заполненные ячейки в первой строке: " & Range(Cells(1, 1), Cells(1, lLastCol)).Address MsgBox "Адрес последней ячейки диапазона на листе: " & Cells.SpecialCells(xlLastCell).Address End Sub
А такой код выделит диапазон ячеек в столбцах с А по С, определяя последнюю ячейку по столбцу A этого же листа:
Sub Copy_To_Last_Cell() Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Select End Sub
А вот такой код скопирует ячейку B1 в первую пустую ячейку столбца A этого же листа:
Sub Copy_To_Last_Cell() Range("B1").Copy Cells(Rows.Count, 1).End(xlUp).Offset(1) End Sub
Следующим кодом используем инструмент автозаполнение(протягивание) столбца В на основании значения в ячейке B2 и определяя последнюю ячейку для заполнения на основании столбца А
Sub AutoFill_B() Dim lLastRow As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("B2").AutoFill Destination:=Range("B2:B" & lLastRow) End Sub
Важно знать: необходимо помнить, что если ячейка содержит формулу, пусть и возвращающую значение "", Excel не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.
Добрый день,
Помогите написать макрос.
Он должен искать по листу1 значение из ячейки А1 и при нахождении копировать эти данные, данные соседней слева ячейки и данные 1 ячейки в строке на 2 лист соответственно в столбцы А,В,С.
Значений будет много, нужно чтобы вставлял по порядку.
Добрый день,
Помогите написать макрос.
Он должен искать по листу1 значение из ячейки А1 и при нахождении копировать эти данные, данные соседней слева ячейки и данные 1 ячейки в строке на 2 лист соответственно в столбцы А,В,С.
Значений будет много, нужно чтобы вставлял по порядку. konstantinp
Вставил руками во второй лист что должно получиться.
То есть ввожу в ячейку А1 дату, макрос должен искать все такие же даты и копировать из этой строки данные и переносить по порядку на лист2.
Вставил руками во второй лист что должно получиться.
То есть ввожу в ячейку А1 дату, макрос должен искать все такие же даты и копировать из этой строки данные и переносить по порядку на лист2. konstantinp
Ну вот где-то так примерно:
Для запуска нужно обновить дату в А1.
в 11:31 перевложил с небольшой поправочкой
Потом только сообразил, что не совсем то сделал, что просили. Поскольку вопрос уже решен, то исправлять не буду.
Ну вот где-то так примерно:
Для запуска нужно обновить дату в А1.
в 11:31 перевложил с небольшой поправочкой
Потом только сообразил, что не совсем то сделал, что просили. Поскольку вопрос уже решен, то исправлять не буду. _Boroda_
в 11:31 перевложил с небольшой поправочкой
Потом только сообразил, что не совсем то сделал, что просили. Поскольку вопрос уже решен, то исправлять не буду. Автор - _Boroda_
Дата добавления - 27.08.2012 в 10:30
Dim c As Range
Dim d As Range
Dim firstAddress As String
With Sheets(1).Range(Columns(2), Columns(Columns.Count))
Set c = .Find([A1], LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set d = [Лист2].Range("A" & Rows.Count).End(xlUp).Offset(1)
d.Offset(0, 0) = c.Parent.Cells(c.Row, 1)
d.Offset(0, 1) = c.Offset(0, -1)
d.Offset(0, 2) = c
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Dim c As Range
Dim d As Range
Dim firstAddress As String
With Sheets(1).Range(Columns(2), Columns(Columns.Count))
Set c = .Find([A1], LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set d = [Лист2].Range("A" & Rows.Count).End(xlUp).Offset(1)
d.Offset(0, 0) = c.Parent.Cells(c.Row, 1)
d.Offset(0, 1) = c.Offset(0, -1)
d.Offset(0, 2) = c
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Dim c As Range
Dim d As Range
Dim firstAddress As String
With Sheets(1).Range(Columns(2), Columns(Columns.Count))
Set c = .Find([A1], LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set d = [Лист2].Range("A" & Rows.Count).End(xlUp).Offset(1)
d.Offset(0, 0) = c.Parent.Cells(c.Row, 1)
d.Offset(0, 1) = c.Offset(0, -1)
d.Offset(0, 2) = c
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
_Boroda_,
Это мой долбаный рабочий антивирус файлы отбивает с "кодами", остановить его не могу.
Если не сложно, можно в архиве, пазязя)
Gustav,
А можете код расписать?
_Boroda_,
Это мой долбаный рабочий антивирус файлы отбивает с "кодами", остановить его не могу.
Если не сложно, можно в архиве, пазязя)
Gustav,
А можете код расписать? konstantinp
В смысле прокомментировать операторы? Сам цикл я, не мудрствуя лукаво, спёр из хелпа для Find (или FindNext). Можете нажать на них F1 и всё увидите сам. Цикл будет повторяться до тех пор пока не будет снова найдено самое первое значение (контролируется по совпадению адресов текущей и самой первой найденной ячеек) - тогда цикл прекратится. После нахождения очередного значения в цикле происходит заполнение очередной строки на Листе2. Область поиска на Листе1 - начиная со второй колонки (B) и до последней (в первой же колонке у нас само значение для поиска, а также другие заранее заготовленные строки).
В смысле прокомментировать операторы? Сам цикл я, не мудрствуя лукаво, спёр из хелпа для Find (или FindNext). Можете нажать на них F1 и всё увидите сам. Цикл будет повторяться до тех пор пока не будет снова найдено самое первое значение (контролируется по совпадению адресов текущей и самой первой найденной ячеек) - тогда цикл прекратится. После нахождения очередного значения в цикле происходит заполнение очередной строки на Листе2. Область поиска на Листе1 - начиная со второй колонки (B) и до последней (в первой же колонке у нас само значение для поиска, а также другие заранее заготовленные строки). Gustav
В смысле прокомментировать операторы? Сам цикл я, не мудрствуя лукаво, спёр из хелпа для Find (или FindNext). Можете нажать на них F1 и всё увидите сам. Цикл будет повторяться до тех пор пока не будет снова найдено самое первое значение (контролируется по совпадению адресов текущей и самой первой найденной ячеек) - тогда цикл прекратится. После нахождения очередного значения в цикле происходит заполнение очередной строки на Листе2. Область поиска на Листе1 - начиная со второй колонки (B) и до последней (в первой же колонке у нас само значение для поиска, а также другие заранее заготовленные строки). Автор - Gustav
Дата добавления - 27.08.2012 в 12:14
Помогите, плиз, я только начинаю осваивать макросы, задачка в следующем: необходимо найти определеное значение в столбце А, затем сместится в найденной строке на две ячейки (столбец С), скопировать оттуда значение. Как найти я понял:
Dim lRow As Long
lRow = Cells.Find(What:="4122", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
А вот как для Range прописать найденную ячейку, не знаю.
4 ответа
[QUOTE=Nik_221]А вот как для Range прописать найденную ячейку, не знаю.[/QUOTE]
Не вполне ясен смысл этой фразы. Однако может быть Вам надо что-то такое: [color=blue]ActiveCell.Offset(0, 2).Value = ActiveCell.Value[/color]
ActiveCell - это текущая ячейка.
ActiveCell.Offset(0, 2) - это ячейка, смещённая относительно текущей на 2 столбца вправо.
Nik_221, Вот пример, который позволит Вам получить и найденную ячейку (конечно, только в случае наличия искомого значения в столбце "A") и ячейку, расположенную в столбце "C".
Private Sub Obrabotka()
Dim iCell As Range
If TypeOf ActiveSheet Is Worksheet Then
Set iCell = ActiveSheet.Columns(1).Find( _
What:="4122", LookIn:=xlValues, LookAt:=xlWhole) 'LookIn:=xlFormulas, LookAt:=xlPart
If Not iCell Is Nothing Then
MsgBox "Адрес найденной ячейки : " & iCell.Address, , ""
MsgBox "Адрес ячейки в столбце ""C"" : " & iCell(1, 3).Address, , "Var1"
MsgBox "Адрес ячейки в столбце ""C"" : " & iCell.Item(1, 3).Address, , "Var1"
MsgBox "Адрес ячейки в столбце ""C"" : " & iCell.Cells(1, 3).Address, , "Var3"
'Если копирование необходимо, например, из-за параметров форматирования, то
iCell.Item(1, 3).Copy Destination:=iCell.Item(1, 4) 'для примера копируем в столбец "D"
Else
MsgBox "К сожалению, точного соответствия найдено не было", , ""
End If
End If
СПАСИБО, pashulka.
Как смог, разобрался, и сори за возможно некорректный вопрос, но главное что теперь буду дальше пытаться.
И в дагонку еще один вопрос.
Мне необходимо найти ряд значений типа 4122, 4029, 4087, 3897,4197 и тд (они заранее известны), если значение найденно, то скопировать из столбца D значение в буфер и поставить в другой книге, в определенную ячейку, но возможно какого-то значения нет, тогда чтобы продолжался поиск следующих значений, без вывода об ошибки.
Private Sub Obrabotka()
Dim iSource As Worksheet, iDestination As Worksheet, iCell As Range
If TypeOf ActiveSheet Is Worksheet Then
Set iSource = ActiveSheet
'Если есть возможность, то можно использовать полную ссылку, т.е.
'Workbook.Worksheet.Column.Find и обойтись без переменной и проверки (см. выше)
Set iDestination = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
'Используется исключительно в качестве примера (для имитации второй книги)
'В Вашем случае, это будет ссылка на существующую и открытую книгу + лист
For Each iItem In Array("4122", "4029", "4087", "3897", "4197")
Set iCell = iSource.Columns(1).Find(What:=iItem, LookIn:=xlValues, LookAt:=xlWhole)
If Not iCell Is Nothing Then
'Довольствуемся первым найденным значение.
'Для поиска всех значений, нужно добавить ещё FindNext
iRow& = iRow& + 1
iCell.Item(1, 4).Copy Destination:=iDestination.Cells(iRow&, 1)
iDestination.Cells(iRow&, 2).Value = iCell.Item(1, 4).Value
'Для примера используем столбец "A" и "B" (новая книга)
End If
Next
Range.Cut – это метод, который вырезает объект Range (диапазон ячеек) в буфер обмена или перемещает его в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен (перемещен) вырезанный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект вырезается в буфер обмена. |
Для вставки на рабочий лист диапазона ячеек, вырезанного в буфер обмена методом Range.Cut, следует использовать метод Worksheet.Paste.
Метод Range.Copy
Range.Copy – это метод, который копирует объект Range (диапазон ячеек) в буфер обмена или в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен скопированный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект копируется в буфер обмена. |
Метод Worksheet.Paste
Синтаксис
Метод Worksheet.Paste работает как с диапазонами ячеек, вырезанными в буфер обмена методом Range.Cut, так и скопированными в буфер обмена методом Range.Copy.
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон (ячейка), указывающий место вставки содержимого буфера обмена. Если этот параметр не указан, используется текущий выделенный объект. |
Link | Необязательный параметр. Булево значение, которое указывает, устанавливать ли ссылку на источник вставленных данных: True – устанавливать, False – не устанавливать (значение по умолчанию). |
В выражении с методом Worksheet.Paste можно указать только один из параметров: или Destination, или Link.
Для вставки из буфера обмена отдельных компонентов скопированных ячеек (значения, форматы, примечания и т.д.), а также для проведения транспонирования и вычислений, используйте метод Range.PasteSpecial (специальная вставка).
Примеры
Вырезание и вставка диапазона одной строкой (перемещение):
Вырезание ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование и вставка диапазона одной строкой:
Копирование ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование одной ячейки и вставка ее данных во все ячейки заданного диапазона:
18 комментариев для “VBA Excel. Вырезание, копирование и вставка ячеек (диапазонов)”
Странно, что не рассмотрено копирование ячеек, которые Cells.
Например, следующая строка копирует ячейку A1 в B2
а эта делает тоже самое, но демонстрирует,
как можно добавить размер и смещение:
Cells ( 1 , "A" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 ) . Copy Cells ( 2 , "B" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 )
Здравствуйте!
Скажите, как можно копировать на Лист 1, а вставить на Лист 2 диапазон ячеек целиком, либо отсортированный по 1 признаку диапазон(например по какому-то определенному значению, скажем, числу «500» в одном столбце Листа 1)?
Привет, Максим!
Вот пример сортировки таблицы по значению 500 в первом столбце на "Лист6" и копирования диапазона с "Лист6" на "Лист4" :
Спасибо большое, только я, наверно, неправильно описал ситуацию.
Сперва из эталонной таблицы Лист 1 копируем весь список на существующий Лист4.
Затем, пропускаем шапку, и, начиная со строки7, уже на Лист4 ищем строку, у которой в столбце 5 стоит число 500.
Вырезаем эту строку и вставляем на позицию строки 7. т.е первая после шапки.
Ищем следующую строку — вырезаем, потом вставляем на позицию строки 7+1=8 и т.д.
В итоге оставим только те строки, у которых в столбце 5 стоит число «500», остальные удаляются строки.
(пробовал менять местами строки в предыдущем коде, ошибок нет, копирует, переходит на лист 4, но строки на нем не удаляет, циклы пошагово проходят, но ничего не изменяется.)
Читайте также: