Копировать строк найденных в поиске excel vba

Обновлено: 22.01.2025

Ну вот где-то так примерно:
Для запуска нужно обновить дату в А1.

в 11:31 перевложил с небольшой поправочкой

Потом только сообразил, что не совсем то сделал, что просили. Поскольку вопрос уже решен, то исправлять не буду.

Ну вот где-то так примерно:
Для запуска нужно обновить дату в А1.

в 11:31 перевложил с небольшой поправочкой

Потом только сообразил, что не совсем то сделал, что просили. Поскольку вопрос уже решен, то исправлять не буду. _Boroda_

в 11:31 перевложил с небольшой поправочкой

Потом только сообразил, что не совсем то сделал, что просили. Поскольку вопрос уже решен, то исправлять не буду. Автор - _Boroda_
Дата добавления - 27.08.2012 в 10:30

У меня какой-то такой макрик получился (если я правильно понял задание):
[vba]

Dim c As Range
Dim d As Range
Dim firstAddress As String

У меня какой-то такой макрик получился (если я правильно понял задание):
[vba]

Dim c As Range
Dim d As Range
Dim firstAddress As String

[/vba] Автор - Gustav
Дата добавления - 27.08.2012 в 11:21 Gustav,
Спасибо! то что нужно!
_Boroda_,
Что то не могу открыть "ошибка 404" Gustav,
Спасибо! то что нужно!
_Boroda_,
Что то не могу открыть "ошибка 404" konstantinp _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

Gustav,
А если "Тоже текст5" будет не в столбце"А", а в строке "1" над "Текст"?

d.Offset(0, 0) = c.Parent.Cells(c.Row, 1) - как тут сделать не столбец, а верхнюю строку?

Да, и еще я вставляю в книгу, где много листов - выдает ошибку.

Gustav,
А если "Тоже текст5" будет не в столбце"А", а в строке "1" над "Текст"?

d.Offset(0, 0) = c.Parent.Cells(c.Row, 1) - как тут сделать не столбец, а верхнюю строку?

Да, и еще я вставляю в книгу, где много листов - выдает ошибку. konstantinp

d.Offset(0, 0) = c.Parent.Cells(c.Row, 1) - как тут сделать не столбец, а верхнюю строку?

Да, и еще я вставляю в книгу, где много листов - выдает ошибку. Автор - konstantinp
Дата добавления - 27.08.2012 в 14:06

А если "Тоже текст5" будет не в столбце"А", а в строке "1" над "Текст"?
d.Offset(0, 0) = c.Parent.Cells(c.Row, 1) - как тут сделать не столбец, а верхнюю строку?

d.Offset(0, 0) = c.Parent.Cells(1, c.Column - 1)

Да, и еще я вставляю в книгу, где много листов - выдает ошибку.

Наверное, тогда надо Sheets(1) заменить на ActiveSheet

Подозреваю также, что надо область поиска подкрутить. Как теперь надо? Всё, кроме первой строки? Или даже всё, кроме первой строки и первого столбца? Хорошо бы пример измененного файла.

P.S. Если кроме первой строки и первого столбца, то замените строку

With Sheets(1).Range(Columns(2), Columns(Columns.Count))

With ActiveSheet.Range(Cells(2, 2), Cells.SpecialCells(xlCellTypeLastCell))

d.Offset(0, 0) = c.Parent.Cells(1, c.Column - 1)

Да, и еще я вставляю в книгу, где много листов - выдает ошибку.

Наверное, тогда надо Sheets(1) заменить на ActiveSheet

P.S. Если кроме первой строки и первого столбца, то замените строку

With Sheets(1).Range(Columns(2), Columns(Columns.Count))

With ActiveSheet.Range(Cells(2, 2), Cells.SpecialCells(xlCellTypeLastCell)) Gustav

d.Offset(0, 0) = c.Parent.Cells(1, c.Column - 1)

Да, и еще я вставляю в книгу, где много листов - выдает ошибку.

Наверное, тогда надо Sheets(1) заменить на ActiveSheet

P.S. Если кроме первой строки и первого столбца, то замените строку

With Sheets(1).Range(Columns(2), Columns(Columns.Count))

With ActiveSheet.Range(Cells(2, 2), Cells.SpecialCells(xlCellTypeLastCell)) Автор - Gustav
Дата добавления - 27.08.2012 в 15:05

Dim wks As Worksheet
Dim c As Range
Dim d As Range
Dim firstAddress As String

For Each wks In ThisWorkbook.Worksheets

If wks.Name <> "Лист2" Then

Dim wks As Worksheet
Dim c As Range
Dim d As Range
Dim firstAddress As String

For Each wks In ThisWorkbook.Worksheets

If wks.Name <> "Лист2" Then

Dim wks As Worksheet
Dim c As Range
Dim d As Range
Dim firstAddress As String

такая вот проблемка. в первом листе "массив", в первой колонке даты без перемешивания (4 строки 16.06.2011, 1 строка 17.06.2011 затем 11 строк 20.06.2011 и т.д.) мне нужно определенную дату найти, выделить все строки с искомым и скопировать на другой лист! помогите ЭЛЕКТРО-ЧАЙНИКУ..)))

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

Поиск с выделением найденных данных
Листинг 2.32. Выделение найденных данных
__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Поиск по значению и вывод строк из таблицы на другой лист Excel
Люди, хелп. Срочно! Нужно в Excel или дописать модуль или что-то с формулами нахимичить. Есть.

Поиск и копирование всех строк с указанным значением на указанный лист
День добрый! Есть таблица с множеством строк порядка (1500-2000), вписал в нее код с созданием.

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

В моём коде предполагается, что даты идут с первой строки, но чтобы порядок был правильный, нужно, чтобы даты шли со второй строки (потому что поиск начинается после ячейки A1 и ячейка A1 просматривается в последнюю очередь как это ни странно).

. чего то не пашит!! я сослался на свои листы:

Set Лист1 = Sheets(моя работа с базой)
Set Лист2 = Sheets(моя работа на сегодня)

и все равно после старта макроса проходит 1 секунда и появляется MsgBox "Работа сделана". хотя вообще ни чего не изменилось. даже активные ячейки остались прежними. а хотя запрашиваемые данные в первой странице точно есть.

а почему они не должны остаться - это же форум программистов, а не форум Excel.

maksimon,
у меня работает ваш код. Я думаю, что ошибка может крыться в строке 19 вашего кода. Протестируйте код, используя просто дату (как у меня в коде), а не ссылку на ячейку.

Или же ошибка связана с тем, что что-то не является типом данных Дата. Мой код написан с учётом того, что дата является датой, а не просто информацией. Чтобы проверить, какой тип информации у вас: щ. правой кн. мыши по ячейке с датой - Формат ячеек. Тип данных должен быть Дата. Если у вас другой, то нужно код переделать. Думаю, надо в строке 5 моего кода заменить Date на String.

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

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке "A" ячейку, содержащую "123" можно примерно так:

Минусами этого так сказать "классического" способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.

Поиск функцией Find

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

Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист "Данные";
2-я строка: Осуществляем поиск значения "123" в колонке "A", результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае - будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What - Строка с текстом, который ищем или любой другой тип данных Excel

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

LookIn - Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt - Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder - Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection - Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase - Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte - Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat - Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать "далее") или FindPrevious (искать "назад").

Примеры поиска функцией Find

Пример 1: Найти в диапазоне "A1:A50" все ячейки с текстом "asd" и поменять их все на "qwe"

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

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

Пример 3: Продолжение поиска с использованием Find с параметром After.

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом "курсив" и поменять их формат на обычный (не "курсив")

Хочу обратить внимание на то, что в этом примере я не стал использовать "защиту от зацикливания", как в Примерах 2 и 3, т.к. шрифт меняется и после "прохождения" по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

Поиск последней заполненной ячейки с помощью Find

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

Пример 5: Найти последнюю колонку и столбец, заполненные данными

Для поиска функцией Find по маске (шаблону) можно применять символы:
* - для обозначения любого количества любых символов;
? - для обозначения одного любого символа;

- для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

Поиск в скрытых строках и столбцах

Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т.е. нужно использовать LookIn:=xlFormulas

Поиск даты с помощью Find

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

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

Пример 8: Найти 1 марта 2018 г.

Искать часть даты - сложнее. Например, чтобы найти все ячейки, где месяц "март", недостаточно искать "03" или "3". Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел - это выбрать формат в котором месяц прописью для ячеек с датами и искать слово "март" в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

Книги по теме:

Посмотреть все книги по программированию

Комментарии к статье:

10.09.17 Дмитрий	Очень толковая и полезная статья. Помогла мне существенно ускорить мой код. Спасибо!
23.11.17 Гость	Спасибо, хорошая статья.
03.12.17 Владимир	Спасибо! Использую в своих проектах.
07.12.17 Эд	Спасибо, очень пригодилась Ваша статья!
19.01.18 Николай	.find не ищет значение в ячейке, если ячейка в скрытой строке. .match позволяет не беспокоится о том, что на листах с источниками данных строка с искомым значением будет скрыта из-за установленного фильтра.
05.02.18 Владимир	Большое спасибо! Очень толково и понятно.
11.03.18 Гость	Здравствуйте, А если мне требуется найти ячейку в определенном столбце с определенным значением, например "строка 1" и если я нахожу такую, то через несколько строк(неизвестно сколько) мне нужно раскрасить следующую ячейку в другом столбце(самую ближайшую) и остановить цикл, и опять по новой искать дальше "строка 1". Можете посоветовать?
26.03.18 Гость	Спасибо! Все бы так описывали! Все доступно и понятно))
23.05.18 Аркадий	В VBA я новичок. Активно использую интернет для своих вопросов, однако таких информативных, лаконичных и простых в понимании сайтов не много. Огромное спасибо автору! Адрес уже в закладках.
21.07.18 Гость	Спасибо! Уже несколько недель искала подобное!
25.07.18 Joann	Метод .find (в случае далнейших множественных обращений к этому механизму) разумно вынести в отдельную функцию, как из управляющей процедуры передать в эту функцию ее параметры (обязательный текстовый параметр 'what:=' - передается без проблем, а вот значения ('xlValues', 'xlWhole', . ) для 'LookIn:=', 'LookAt:=', . - приводят к ошибке)?
15.08.18 Марат	Спасибо за статью, пополнил свои знания в части метода Find. Простые переборы хороши на небольших диапазонах. А когда нужно обработать сотни тысяч ячеек, Find - хороший инструмент!
28.08.18 Гость	Познавательно и подробно. Спасибо за статью.
16.10.18 Гость	Хорошая статья. Спасибо
29.10.18 Sega	Полезная статейка. Спасибо.
14.11.18 Гость	Статья для начинающих, а тонкости поиска по дате нет ни одного примера
02.02.19 Ибрагим	Чушь полная, плагиат!
01.03.19 inexsu.wordpress.com	Loop While Not c Is Nothing And c.Address <> firstResult Когда c станет Nothing, c.Address даст ошибку Решение https://inexsu.wordpress.com/2018/03/05/range-findnext-method/
01.03.19 Администратор	Вы правы, такое действительно может произойти, например, если менять значения найденных ячеек. Т.е., например, ищем значения "asd" и меняем их на "qwe". При замене последнего значения, FindNext ничего уже не найдет, вернет Nothing и произойдет ошибка в условии. Внес изменения в статье. Спасибо вам за подсказку.
07.04.19 Гость	Добрый день. есть 2 таблица на разных листах. макрос нашел нужные ячейки с ИНН по 1 условию, затем мне надо чтобы макрос нашел в таблице 2 на листе 2 все названия клиентов с инн по условию 1 из таблицы 1 с листа 1. как тогда использовать find.
07.04.19 Гость	Лучше использовать функцию ВПР
21.05.19 Гость	Очень подробная статья. Отдельное спасибо за множество примеров.
17.10.19 Михаил	Спасибо большое все понятно доступно и очень полезно
31.10.19 Гость	Спасибо.
01.11.19 Гость	ку
01.11.19 Гость	2 раза
19.11.19 Kamol	Отлично. Поиск с двумя значениями Пример в колонке А="123" и В="456"
25.12.19 Гость	Спасибо!
05.03.20 Гость	Добрый день! Спасибо за код автору Вопрос

Как сделать поиск слова по, но с любым регистром и любым набором символов
Пример: Я ввожу слово через Textbox
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"

Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.

Синтаксис метода Range.Find

Expression . Find ( What , After , LookIn , LookAt , SearchOrder , SearchDirection , MatchCase , MatchByte , SearchFormat )

Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.

В скобках перечислены параметры метода, среди них только What является обязательным.

Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.

Если необходимо найти следующие ячейки, содержащие поисковую фразу, используется метод Range.FindNext.

Параметры метода Range.Find

xlValues (-4163) – значения;
xlComments (-4144) – примечания*;
xlNotes (-4144) – примечания*;
[xlFormulas (-4123) – формулы]**.

xlWhole (1) – полное совпадение;
xlPart (2) – частичное совпадение.

xlByRows (1) – поиск по строкам;
xlByColumns (2) – поиск по столбцам.

xlNext (1) – поиск вперед;
xlPrevious (2) – поиск назад.

False (0) – поиск без учета регистра (по умолчанию);
True (1) – поиск с учетом регистра.

False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.

* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes .
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.

В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.

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