Как найти номер строки в excel по значению ячейки vba

Обновлено: 07.07.2024

Обычно мы можем определить номер строки и номер столбца ячейки по ее адресу. Например, адрес A2 означает, что он находится в столбце 1 и строке 2. Но может быть немного сложно определить номер столбца ячейки NK60. И если у вас есть только адрес столбца или адрес строки ячейки, как определить номер строки или номер столбца? Эта статья покажет вам решения.

Если вы знаете только адрес, как вы можете определить номер строки и столбца?

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

Если адрес ячейки NK60, он показывает номер строки 60; и вы можете получить столбец с формулой = Столбец (NK60) .

Конечно, вы можете получить номер строки по формуле = Ряд (NK60) .

Если вы знаете только адрес столбца или строки, как вы можете определить номер строки или столбца?

Иногда вы можете знать значение в определенном столбце или строке и хотите определить его номер строки или номер столбца. Вы можете получить их с помощью функции Match.

Предположим, у вас есть таблица, как показано на следующем снимке экрана.

Предположим, вы хотите узнать номер строки «чернила"И вы уже знаете, что он находится в столбце A, вы можете использовать эту формулу = ПОИСКПОЗ ("чернила"; A: A; 0) в пустой ячейке, чтобы получить номер строки. После ввода формулы и нажатия клавиши Enter отобразится номер строки ячейки, содержащей "чернила".

Предположим, вы хотите узнать номер столбца «чернила"И вы уже знаете, что он находится в строке 4, вы можете использовать эту формулу = ПОИСКПОЗ ("чернила"; 4: 4,0) в пустой ячейке, чтобы получить номер строки. После ввода формулы и нажатия клавиши Enter отобразится номер строки ячейки, содержащей "чернила".

Выберите всю строку / столбец, если значения ячеек соответствуют определенному значению в Excel

Сравнение, чтобы вернуть номер строки значения столбца, если значение ячейки соответствует определенному значению, Kutools for Excel's Выбрать определенные ячейки Утилита предоставляет пользователям Excel другой выбор: выбрать всю строку или весь столбец, если значения ячеек соответствуют определенному значению в Excel. И номер строки в крайнем левом углу или буква столбца вверху будут выделены, как показано на скриншоте ниже. Легче и понятнее в работе! Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Поиск какого-либо значения в ячейках 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
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"

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

Например, есть столбец неких чисел A (числа вразнобой, но не повторяются), есть ячейка D1, в которую вводится значение для поиска и есть ячейка D3, в которую нужно вывести результат.
Как сделать так, чтобы при вводе в ячейку D1 некоторого числа производился поиск аналогичного числа в столбце А и в ячейку D3 выводился номер строки, в которой находится найденное число?

Пример (смотрите прилагаемый файл):
В ячейку D1 вручную вводится число 515, некой формуле нужно найти такое же число в столбце А - так как оно находится в ячейке A7, то в ячейку D3 нужно записать число 7.
Если в ячейку D1 ввести число 443 - то так как оно находится в ячейке A2, то в ячейку D3 нужно записать число 2 и так далее.

Заранее огромное спасибо за помощь!

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

Заранее огромное спасибо за помощь! DM11

Заранее огромное спасибо за помощь! Автор - DM11
Дата добавления - 01.11.2019 в 01:06

Кто не знает метод 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.

Как найти номер строки в excel по значению ячейки vba

Если вы знаете только адрес, как вы можете определить номер строки и столбца?

Если вы знаете только адрес столбца или строки, как вы можете определить номер строки или столбца?

Выберите всю строку / столбец, если значения ячеек соответствуют определенному значению в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рекомендуемые статьи