Excel vba поиск на другом листе

Обновлено: 07.01.2025

Хотите создавать решения, которые расширяют возможности 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 и обратная связь.

Метод 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.

В этой статье рассказывается о том, как искать значения и возвращать данные из другой книги, а также как находить / искать значения из другой книги. Здесь я подробно расскажу о трех решениях.

Данные Vlookup и возвращаемые значения из другой книги в Excel

Например, вы создаете таблицу «Покупка фруктов» в Excel, и теперь вам нужно просмотреть фрукты из другой книги и вернуть соответствующие цены, как показано на скриншотах ниже. Здесь я расскажу вам, как решить эту проблему с помощью функции VLOOKKUP в Excel.

1. Откройте обе книги, из которых вы будете просматривать значения и возвращать значения.

2. Выберите пустую ячейку, в которую вы вернете цену, введите формулу = ВПР (B2; [Price.xlsx] Sheet1! $ A $ 1: $ B $ 24,2; FALSE) в него, а затем перетащите его маркер заполнения, чтобы применить эту формулу к диапазону по мере необходимости.

Заметки:
(1) В приведенной выше формуле B2 - это плод, который вы будете искать в другой книге, Price.xlsx указывает имя файла книги, из которой вы будете искать, Sheet1 означает имя листа, из которого вы будете искать, и A $ 1 : $ B $ 24 - это диапазон, из которого вы будете искать. Вы можете изменить их по своему усмотрению.
(2) После закрытия книги, из которой вы искали, формула автоматически обновится до = ВПР (B2; 'W: \ test \ [Price.xlsx] Sheet1'! $ A $ 1: $ B $ 24,2, FALSE) , W: \ test \ - это путь сохранения книги, из которой вы искали.

Пока все цены вернулись правильно, как показано на скриншоте слева. И эти цены будут обновляться автоматически, если исходная книга, которую вы искали, изменилась.

Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем!
Подробнее . Бесплатная пробная версия

Данные Vlookup и возвращаемые значения из другой закрытой книги с VBA

Может возникнуть путаница при настройке пути сохранения, имени файла и рабочего листа в функции VLOOKUP. Этот метод позволит легко решить эту проблему с помощью VBA.

1. нажмите другой + F11 клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модуль, а затем вставьте ниже код VBA в открывающееся окно модуля.

VBA: данные Vlookup и возвращаемые значения из другой закрытой книги

Внимание: Этот VBA будет возвращать значения в столбце, который находится на 2 столбца позади выбранного столбца. Например, я выбираю столбец B при применении этого VBA, значения возвращаются в столбце D. Если вам нужно изменить столбец назначения, узнайте код xRg.Offset (0, 2) .Formula = xString & GetColumn (xFCell.Column + 1) & "$" & xFCell.Row и заменить 2 на другой номер по мере необходимости.

3. нажмите F5 ключ или щелкните Запустите кнопку для запуска этого VBA.

4. В открывшемся диалоговом окне укажите диапазон данных, который вы будете искать, и щелкните значок OK кнопку.

5. Теперь выберите книгу, в которой вы будете искать значения в открывшемся диалоговом окне «Выбор книги», и нажмите кнопку Откройте кнопку.

Теперь все выбранные значения ищутся в указанной закрытой книге, и соответствующие значения возвращаются в указанном столбце. Смотрите скриншот:

Кто не знает метод FIND в Excel? Я уверен, что все знают, кто имеет дело с рабочими листами Excel. НАЙТИ или популярную комбинацию клавиш Ctrl + F найдет слово или контент, который вы ищете во всей рабочей таблице, а также во всей рабочей книге. Когда вы говорите, что найти означает, что вы находите в ячейках или диапазонах не так ли? Да, правильный метод поиска является частью ячеек или диапазонов в Excel, а также в VBA.

Аналогично, в VBA Find у нас есть опция под названием FIND function, которая может помочь нам найти искомое значение. В этой статье я познакомлю вас с методологией FIND в VBA.

Формула для поиска функции в Excel VBA

В обычной рабочей таблице Excel мы просто нажимаем сочетание клавиш Ctrl + F, чтобы найти содержимое. Но в VBA нам нужно написать функцию, чтобы найти контент, который мы ищем. Хорошо, тогда давайте посмотрим на синтаксис FIND.

Я знаю, что происходит в вашем уме, вы потерялись, глядя на этот синтаксис, и вы ничего не понимаете. Но не о чем беспокоиться, прежде чем я объясню вам синтаксис, позвольте мне познакомить вас с обычным окном поиска.

Если вы наблюдаете за тем, что есть в обычных Ctrl + F, все также присутствует в синтаксисе VBA Find. Теперь посмотрим, о чем говорит каждое слово в синтаксисе.

Что: просто то, что вы ищете. Здесь нужно упомянуть контент, который мы ищем.

После: после какой ячейки вы хотите искать.

LookIn: где искать то, что вы ищете. Например, формулы, значения или комментарии. Параметры: xlFormulas, xlValues, xlComments.

LookAt: ищите ли вы весь контент или только часть контента. Параметры: xlWhole, xlPart.

SearchOrder: вы ищете в строках или столбцах. xlByRows или xlByColumns.

SearchDirection: вы смотрите на следующую или предыдущую ячейку. xlNext, xlPrevious.

MatchCase: содержимое, которое вы ищете, чувствительно к регистру или нет. Правда или ложь.

MatchByte: это только для двухбайтовых языков. Правда или ложь.

SearchFormat: вы ищете форматирование. Если вы ищете формат, вам нужно использовать метод Application.FindFormat .

Это объяснение синтаксиса метода VBA FIND. Помимо первого параметра все необязательно. В разделе примеров мы увидим, как использовать этот метод FIND в кодировании VBA.

Как использовать функцию поиска VBA Excel?

Мы научимся использовать функцию VBA Find Excel на нескольких примерах.

Вы можете скачать этот шаблон VBA Find Excel здесь - VBA найти шаблон Excel

Функция поиска VBA - пример № 1

Прежде всего позвольте мне объяснить вам простой пример использования свойства FIND и найти контент, который мы ищем. Предположим, ниже приведены данные, которые вы имеете в своем листе Excel.

Шаг 1: Из этого я хочу найти имя Джон, давайте откроем Visual Basic и начнем кодирование.

Код:

Шаг 2: Здесь вы не можете начать слово FIND, потому что FIND является частью свойства RANGE. Итак, во-первых, нам нужно упомянуть, где мы смотрим, т.е. Range.

Шаг 3: Итак, сначала укажите диапазон, в котором мы ищем. В нашем примере наш диапазон от B2 до B11.

Код:

Шаг 4: После упоминания диапазона поставьте точку (.) И введите FIND. Вы должны увидеть свойство НАЙТИ.

Шаг 5: Выберите свойство НАЙТИ и откройте скобку.

Шаг 6: Наш первый аргумент - это то, что мы ищем. Чтобы выделить аргумент, мы можем передать аргумент наподобие What: =, это будет полезно для определения того, на какой параметр мы ссылаемся.

Код:

Шаг 7: Последняя часть после нахождения слова, что мы хотим сделать. Нам нужно выбрать слово, поэтому передайте аргумент как. Выберите

Код:

Шаг 8: Затем запустите этот код, используя клавишу F5 или вручную, как показано на рисунке, чтобы он выбрал первое найденное слово Джонсон, которое содержит слово Джон.

Функция поиска VBA - пример № 2

Теперь я покажу вам, как найти слово комментария, используя метод find. У меня есть данные, и в трех ячейках у меня есть комментарий.

В ячейках с красным флажком есть комментарии. Из этого комментария я хочу найти слово «Без комиссии».

Шаг 1: начните код с упоминания диапазона («D2: D11») и поставьте точку (.) И введите Find

Код:

Шаг 2: В аргументе WHAT введите слово «Без комиссии».

Код:

Шаг 3: Пропустите часть After и выберите часть LookIn. В части LookIn мы ищем это слово в комментариях, поэтому выберите xlComments, а затем передайте аргумент как .Select

Код:

Шаг 4: Теперь запустите этот код, используя клавишу F5 или вручную, как показано на рисунке, чтобы он выбрал ячейку с комментарием «Без комиссии». В ячейке D9 есть упомянутый комментарий.

Если искомое слово не будет найдено в диапазоне, мы предоставили код VBA, который будет возвращать ошибку, подобную этой.

Чтобы показать пользователю, что искомое значение недоступно, нам понадобится приведенный ниже код.

То, что нужно запомнить

VBA FIND является частью свойства RANGE, и вам нужно использовать FIND только после выбора диапазона.
В FIND первый параметр является обязательным (What), кроме этого все остальное является необязательным.
Если вам нужно найти значение после определенной ячейки, вы можете упомянуть ячейку в параметре After синтаксиса Find.