Vba excel find если не найдено
Вы сами написали макрос и кому-то выслали. Макрос хороший и рабочий. Вы сами проверили и перепроверили. Но тут Вам сообщают - "Макрос не работает". Выдает ошибку - Can't find project or library. Вы запускаете файл - нет ошибки. Еще раз - нет. Как ни пытаетесь, какие данные не подсовываете, а ошибки такой нет. Вы уверены, что файл рабочий полностью(и верно - ведь у Вас работает), а Вам пытаются доказать обратное. Вы начинаете долго объяснять как пользоваться, что делать и т.д. и т.п. Ошибка не исчезает. Что делать-то? В чем проблема? Даже офис переустановили - у Вас работает, у них нет. А проблема проста: как и любой программе, VBA нужно иметь свой набор библиотек и компонентов, посредством которых он взаимодействует с Excel(и не только). И в разных версиях Excel эти библиотеки и компоненты могут различаться. И когда Вы делаете у себя программу, то VBA ставит ссылку(или же Вы сами) на какой-либо компонент либо библиотеку, которая может отсутствовать на другом компьютере. Вот тогда и появляется эта ошибка. Что же делать? Все очень просто:
- Открываете редактор VBA
- Идете в Tools-References
- Находите там все пункты, напротив которых красуется MISSING. Снимаете с них галочки.
- Жмете Ок.
- Сохраняете файл.
Эти действия необходимо проделать, когда выполнение кода прервано и ни один код проекта не выполняется. Возможно, придется перезапустить Excel. И все это должен проделать человек, у которого данная ошибка возникла. Это не всегда удобно. А поэтому лично я рекомендовал бы не использовать сторонние библиотеки и раннее связывание, если это не вызвано необходимостью. Всегда проверяйте ссылки в файлах перед отправкой получателю. Оставьте там лишь те ссылки, которые необходимы, либо которые присутствуют на всех версиях. Смело можно оставлять следующие(это касается именно VBA-Excel):
- Visual Basic for application(эту ссылку Вы попросту не сможете отключить);
- Microsoft Excel XX.0 Object Library(вместо X версия приложения - 12, 11 и т.д.)-эту ссылку Вы не сможете отключить;
- Microsoft Forms X.0 Object Library(эту ссылку Вы не сможете отключить);
- OLE Automation(хотя она тоже не нужна - но и вреда никакого).
Может я перечислил не все - но эти точно имеют полную совместимость между разными версиями Excel.
Еще, для автоматического поиска и отключения ошибочных ссылок на такие библиотеки можно делать и макросом:
Sub Remove_MISSING() Dim oReferences As Object, oRef As Object Set oReferences = ThisWorkbook.VBProject.References For Each oRef In oReferences If (oRef.IsBroken) Then oReferences.Remove Reference:=oRef Next End Sub
Но для работы этого макроса необходимо:
- проставить доверие к проекту VBA:
Excel 2010-2019 - Файл-Параметры-Центр управления безопасностью-Параметры макросов-поставить галочку "Доверять доступ к объектной модели проектов VBA"
Excel 2007 - Кнопка Офис-Параметры Excel-Центр управления безопасностью-Параметры макросов-поставить галочку "Доверять доступ к объектной модели проектов VBA"
Excel 2003 - Сервис - Параметры-вкладка Безопасность-Параметры макросов-Доверять доступ к Visual Basic Project - проект VBA не должен быть защищен.
Так же Can't find project or library возникает если у Вас просто не подключена какая-либо библиотека, которая используется в коде. Тогда не будет MISSING. Надо просто определить, в какую библиотеку входит константа, объект или свойство, которое выделяет редактор при выдаче ошибки, и подключить эту библиотеку.
Например, есть такой кусок кода:
Sub CreateWordDoc() Dim oWordApp As Word.Application Set oWordApp = New Word.Application oWordApp.Documents.Add
если библиотека Microsoft Excel XX.0 Object Library (вместо XX версия приложения - 11, 12, 16 и т.д.) не подключена, то будет подсвечена строка oWordApp As Word.Application .
Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.
Синтаксис
выражение.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
выражение: переменная, представляющая объект Range.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
What | Обязательный | Variant | Искомые данные. Может быть строкой или любым типом данных Microsoft Excel. |
After | Необязательный | Variant | Ячейка, после которой нужно начать поиск. Соответствует положению активной ячейки, когда поиск выполняется из пользовательского интерфейса. |
Обратите внимание, что параметр After должен быть одной ячейкой в диапазоне. Помните, что поиск начинается после этой ячейки; указанная ячейка не входит в область поиска, пока метод не возвращается обратно в эту ячейку.
Возвращаемое значение
Объект Range, представляющий первую ячейку, в которой обнаружены требуемые сведения.
Примечания
Этот метод возвращает значение Nothing, если совпадения не найдены. Метод Find не влияет на выделенный фрагмент или активную ячейку.
Параметры для аргументов LookIn, LookAt, SearchOrder и MatchByte сохраняются при каждом использовании этого метода. Если не указать значения этих аргументов при следующем вызове метода, будут использоваться сохраненные значения. Установка этих аргументов изменяет параметры в диалоговом окне Найти, а изменение параметров в диалоговом окне Найти приводит к изменению сохраненных значений, которые используются, если опустить аргументы. Чтобы избежать проблем, устанавливайте эти аргументы явным образом при каждом использовании этого метода.
Для повторения поиска используйте методы FindNext и FindPrevious.
Когда поиск достигает конца указанного диапазона поиска, он возвращается в начало диапазона. Чтобы остановить поиск при этом возврате, сохраните адрес первой найденной ячейки, а затем проверьте адрес каждой последующей найденной ячейки, сравнив его с этим сохраненным адресом.
Чтобы найти ячейки, отвечающие более сложным шаблонам, используйте инструкцию For Each. Next с оператором Like. Например, следующий код выполняет поиск всех ячеек в диапазоне A1:C5, где используется шрифт, имя которого начинается с букв Cour. Когда Microsoft Excel находит соответствующее значение, шрифт изменяется на Times New Roman.
Примеры
В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие значение 2, и изменить значение ячейки на 5. Таким образом, значения 1234 и 99299 содержали 2 и оба значения ячеек станут 5.
В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие подстроку "abc", и изменить ее на "xyz".
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.
Syntax
expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
expression A variable that represents a Range object.
Parameters
Name | Required/Optional | Data type | Description |
---|---|---|---|
What | Required | Variant | The data to search for. Can be a string or any Microsoft Excel data type. |
After | Optional | Variant | The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. |
Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell.
Return value
A Range object that represents the first cell where that information is found.
Remarks
This method returns Nothing if no match is found. The Find method does not affect the selection or the active cell.
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.
Use the FindNext and FindPrevious methods to repeat the search.
When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.
To find cells that match more complicated patterns, use a For Each. Next statement with the Like operator. For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters Cour. When Microsoft Excel finds a match, it changes the font to Times New Roman.
Examples
This example finds all cells in the range A1:A500 in worksheet one that contain the value 2, and changes the entire cell value to 5. That is, the values 1234 and 99299 both contain 2 and both cell values will become 5.
This example finds all cells in the range A1:A500 on worksheet one that contain the substring "abc" and then replaces "abc" with "xyz".
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Метод Range.Find находит первую ячейку в заданном диапазоне по точному или частичному совпадению условия поиска с ее значением, формулой или примечанием. На этом работа метода Find заканчивается. При новом запуске он найдет ту же ячейку.
Что делать, если в диапазоне есть еще ячейки, соответствующие условию поиска, и их тоже необходимо найти?
Для этих целей предназначен метод Range.FindNext . Он продолжает в диапазоне поиск следующей ячейки, соответствующей условию, заданному в предыдущей строке с методом Find . Для того, чтобы найти все такие ячейки, метод FindNext повторяется с помощью одного из циклов VBA Excel.
Синтаксис
- Expression – выражение (переменная), возвращающее объект Range, в котором осуществляется поиск.
- After – необязательный аргумент, представляющий из себя единственную ячейку диапазона, после которой начнется поиск. Если аргумент не указан, поиск начнется после левой верхней ячейки диапазона. В обоих случаях, ячейка по умолчанию (левая верхняя) или ячейка, заданная параметром After, в поиске не участвует.
Пример использования
Условие
В таблице с названиями басен необходимо выбрать басни, в названия которых входит слово Лев:
Решение
Результат
Расшифровка
1. Объявляем переменные:
Dim myCell As Range, adr As String, str As String
- myCell – объектная переменная, которой присваивается ссылка на ячейку, найденную методами Find и FindNext ;
- adr – адрес первой найденной ячейки, который нужен для остановки цикла после однократного прохождения по ячейкам указанного диапазона;
- str – в эту переменную записывается содержимое найденных ячеек.
2. Ищем первую ячейку со словом Лев в диапазоне Range("A1:C9") и присваиваем ссылку на нее переменной myCell :
Set myCell = .Find("Л?в", MatchCase:=1)
В искомой строке («Л?в») используем подстановочный знак (?), который заменяет любую букву, чтобы найти слова с корнями «Лев» и «Льв».
Так как животные в названиях басен пишутся с заглавной буквы, в искомой строке тоже используем заглавную букву и поиск с учетом регистра ( MatchCase:=1 ), чтобы не были найдены ячейки со словами «плов«, «клавиатура» и т.д. Хотя ячейки со словами «Левша», «Лавка» и подобные, начинающиеся с большой буквы, будут найдены.
3. Если переменная myCell не содержит Nothing , значит первая ячейка с искомой строкой найдена. Присваиваем ее значение переменной str , а ее адрес – переменной adr .
4. С помощью цикла Do. Loop Until. и метода FindNext находим в диапазоне все остальные ячейки, соответствующие критерию поиска, заданному в строке с методом Find . Добавляем содержимое найденных ячеек в переменную str .
Переменная myCell используется повторно в течение всей работы цикла: Set myCell = .FindNext(myCell) .
Цикл и добавление значений в переменную str завершаются при возврате в первоначально найденную ячейку, адрес которой мы записали (используем условие myCell.Address = adr ).
5. По окончании работы цикла выводим содержимое переменной str в информационном окне MsgBox.
Поиск какого-либо значения в ячейках 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
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"
Читайте также: