Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Find vba excel описание

Поиск на листе Excel

Поиск какого-либо значения в ячейках 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: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

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

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

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

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

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

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

Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы «т», при этом после этого слова может следовать любой текст.

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

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

Читать еще:  Vba excel добавить лист с именем

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

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

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

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

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

  • Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
  • В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

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

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

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

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

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

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

VBA Excel. Метод Find объекта Range

Метод Find объекта Range для поиска ячейки по ее данным в VBA Excel. Синтаксис и компоненты. Знаки подстановки для поисковой фразы. Простые примеры.

Предназначение и синтаксис метода Range.Find

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

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

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

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

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

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

НаименованиеОписание
Обязательный параметр
WhatДанные для поиска, которые могут быть представлены строкой или другим типом данных Excel. Тип данных параметра – Variant.
Необязательные параметры
AfterЯчейка, после которой следует начать поиск.
LookInУточняет область поиска. Список констант xlFindLookIn:

  • xlValues (-4163) – значения;
  • xlComments (-4144) – примечания*;
  • xlNotes (-4144) – примечания*;
  • [xlFormulas (-4123) – формулы]**.
LookAtПоиск частичного или полного совпадения. Список констант xlLookAt:

  • xlWhole (1) – полное совпадение;
  • xlPart (2) – частичное совпадение.
SearchOrderОпределяет способ поиска. Список констант xlSearchOrder:

  • xlByRows (1) – поиск по строкам;
  • xlByColumns (2) – поиск по столбцам.
SearchDirectionОпределяет направление поиска. Список констант xlSearchDirection:

  • xlNext (1) – поиск вперед;
  • xlPrevious (2) – поиск назад.
MatchCaseОпределяет учет регистра:

  • False (0) – поиск без учета регистра (по умолчанию);
  • True (1) – поиск с учетом регистра.
MatchByteУсловия поиска при использовании двухбайтовых кодировок:

  • False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
  • True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.
SearchFormatФормат поиска – используется вместе со свойством Application.FindFormat.

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

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

Знаки подстановки для поисковой фразы

Условные знаки в шаблоне поисковой фразы:

  • ? – знак вопроса обозначает любой отдельный символ;
  • * – звездочка обозначает любое количество любых символов, в том числе ноль символов;

Простые примеры

При использовании метода Range.Find в VBA Excel необходимо учитывать следующие нюансы:

  1. Так как этот метод возвращает объект Range (в виде одной ячейки), присвоить его можно только объектной переменной, объявленной как Variant, Object или Range, при помощи оператора Set.
  2. Если поисковая фраза в заданном диапазоне найдена не будет, метод Range.Find возвратит значение Nothing. Обращение к свойствам несуществующей ячейки будет генерировать ошибки. Поэтому, перед использованием результатов поиска, необходимо проверить объектную переменную на содержание в ней значения Nothing.

В примерах используются переменные:

  • myPhrase – переменная для записи поисковой фразы;
  • myCell – переменная, которой присваивается первая найденная ячейка, содержащая поисковую фразу, или значение Nothing, если поисковая фраза не найдена.

VBA FIND

Excel VBA Find

When we use Find in a normal worksheet we press keyboard shortcut CTRL + F and type the data we need to find and if not desired value we go to the next match, if there is a lot of such matches it is a tedious task but when we use FIND in VBA it does the tasks for us and give us the exact match and it takes three arguments, one is what to find, where to find and where to look at.

Before we move to VBA and start using find function in macros we need to learn first what is a find function in excel. In normal excel in the Home tab under the editing group, we can find a find function which is used to find a string or a value in a cell range or whole worksheet.

When we click on this, we get two options;

One is simple to find,

We can see it has also a mode of options which opens up another feature.

It does the find algorithm with four constraints, Find What, Within, Search and look in.

The second option in excel is to Find and replace which is used when we find a string but what to replace it with any other value,

Find Function Syntax

We have learned above what is Find in basic excel. In VBA we write codes manually but the features are the same as normal excel. First, let us look at the syntax.

If the value we are looking is found with the excel function it returns the cell where the value is and if the value is not found then the object of the function is set to nothing.

Expressions in macros are ranges defined such as range 1 or range 2. What is a keyword for what we want to search a specific value? Lookin is a keyword for what we are trying to search is it a comment or a formula or a string. Similarly, there are other constraints in Find function which are optional. The only mandatory field required is what is a value we are trying to search.

Basically, VBA find Excel has one required argument which is What which value we want to search. The rest of the constraints are optional and there are many constraints in find function. Find function is similar to what a find function is in excel.

The parameter for find function is the range of cells. Like in which range we want to find a value. It can be a few columns or few cells or whole worksheet.

Examples

Example #1

Suppose our data has the following values

We will try to find “Aran” in the same data.

  • To write a VBA code it is necessary to have enabled the developer tab in order to be able to write VBA Codes.

  • We start writing our code by writing the following code as shown below,

  • The sample is the function name given to sub.
  • Find is the string we want the user we want to enter to search.
  • Rng is the variable we took for the range.
  • Now we ask the user to enter the value which looks like the screenshot below,

  • Now we will define our find function in the module.

  • The function finds the value entered by the user in the given range.
  • Now we close the function by the following arguments.

  • Now if we run our code first it asks for a prompt by the user for a value.

  • Once the code is completed it returns the cell to where the data was found.

Example #2

In the above example, there were four unique names but what if there were more than one names in the data, such as consider the below data,

We can see that the name Aran is repeated twice in the above data. If excel has to find the name Aran it will find it in cell A2 and stop, but there is another value similar to that of A2 in cell A6. How to fetch that value? Here comes the syntax of Find(What, After) in help.

After defines cell after which reference we want to search the data.

Let us write the code for the above data.

  • Always remember to enable developer tab from options and then from customizing ribbons to be able to write the code in VBA.
  • In VBA we get Microsoft excel objects which is a module where we write the codes.

  • Previously we were working on sheet 1 now we are working in sheet 2 so select sheet 2 for another module and a blank page appears.

  • Now start writing the code by defining the function first as SUB Sample2() and press enter.

  • Now we have defined our function we will start getting into the main part which is defining our variables.

  • Define what does the Find variable should have,

  • Select the sheets which we are working on which is sheet 2 in this example,

  • Now we will find the text whatever the user enters after A2 cell, so we define our find function as below,

  • Now we close the code by ending the with and if conditions.

What the above code does is search the string after the cell A2 and returns the cell wherever it is found.

Things to Remember

  1. First things first we need to enable the developer tab in order to use VBA.
  2. What is the specific value we need to find?
  3. If the value is not found, the object of the function is set to nothing.

Recommended Articles

This has been a complete guide to VBA Find Function . Here we learn how to use Excel VBA find function with practical examples and downloadable excel sheet. You may also have a look at other articles related to Excel VBA –

VBA Find

Excel VBA Find Function

Who doesn’t know FIND method in excel? I am sure everybody knows who are dealing with excel worksheets. FIND or popular shortcut key Ctrl + F will find the word or content you are searching for in the entire worksheet as well as in the entire workbook. When you say find means you are finding in cells or ranges isn’t it? Yes, the correct find method is part of the cells or ranges in excel as well as in VBA.

Similarly, in VBA Find, we have an option called FIND function which can help us find the value we are searching for. In this article, I will take you through the methodology of FIND in VBA.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

Formula to Find Function in Excel VBA

In regular excel worksheet, we simply type shortcut key Ctrl + F to find the contents. But in VBA we need to write a function to find the content we are looking for. Ok, let’s look at the FIND syntax then.

I know what is going on in your mind, you are lost by looking at this syntax and you are understanding nothing. But nothing to worry before I explain you the syntax let me introduce you to the regular search box.

If you observe what is there in regular Ctrl + F, everything is there in VBA Find syntax as well. Now take a look at what each word in syntax says about.

What: Simply what you are searching for. Here we need to mention the content we are searching for.

After: After which cell you want to search for.

LookIn: Where to look for the thing you are searching For example Formulas, Values, or Comments. Parameters are xlFormulas, xlValues, xlComments.

LookAt: Whether you are searching for the whole content or only the part of the content. Parameters are xlWhole, xlPart.

SearchOrder: Are you looking in rows or Columns. xlByRows or xlByColumns.

SearchDirection: Are you looking at the next cell or previous cell. xlNext, xlPrevious.

MatchCase: The content you are searching for is case sensitive or not. True or False.

MatchByte: This is only for double-byte languages. True or False.

SearchFormat: Are you searching by formatting. If you are searching for format then you need to use Application.FindFormat method.

This is the explanation of the syntax of the VBA FIND method. Apart from the first parameter, everything is optional. In the examples section, we will see how to use this FIND method in VBA coding.

How to Use Excel VBA Find Function?

We will learn how to use a VBA Find Excel function with few examples.

VBA Find Function – Example #1

First up let me explain you a simple example of using FIND property and find the content we are looking for. Assume below is the data you have in your excel sheet.

Step 1: From this, I want to find the name John, let’s open a Visual basic and start the coding.

Code:

Step 2: Here you cannot start the word FIND, because FIND is part of RANGE property. So, firstly we need to mention where we are looking i.e. Range.

Step 3: So first mention the range where we are looking for. In our example, our range is from B2 to B11.

Code:

Step 4: After mentioning the range put a dot (.) and type FIND. You must see FIND property.

Step 5: Select the FIND property and open the bracket.

Step 6: Our first argument is what we are searching for. In order to highlight the argument we can pass the argument like this What:=, this would be helpful to identify which parameter we are referring to.

Code:

Step 7: The final part is after finding the word what we want to do. We need to select the word, so pass the argument as .Select.

Code:

Step 8: Then run this code using F5 key or manually as shown in the figure, so it would select the first found word Johnson which contains a word, John.

VBA Find Function – Example #2

Now I will show you how to find the comment word using the find method. I have data and in three cells I have a comment.

Those cells having red flag has comments in it. From this comment, I want to search the word “No Commission”.

Step 1: Start code with mentioning the Range (“D2:D11”) and put a dot (.) and type Find

Code:

Step 2: In the WHAT argument type the word “No Commission”.

Code:

Step 3: Ignore the After part and select the LookIn part. In LookIn part we are searching this word in comments so select xlComments and then pass the argument as .Select

Code:

Step 4: Now run this code using F5 key or manually as shown in the figure so it will select the cell which has the comment “No Commission”. In D9 cell we have a mentioned comment.

Deal with Error Values in Excel VBA Find

If the word we are searching for does not find in the range we have supplied VBA code which will return an error like this.

In order to show the user that the value you are searching for is not available, we need the below code.

If the above code found value then it shows the value & cell address or else it will show the message as “The Value you are searching for is not available in the supplied range. ”.

Things to Remember

  • VBA FIND is part of the RANGE property & you need to use the FIND after selecting the range only.
  • In FIND first parameter is mandatory (What) apart from this everything else is optional.
  • If you to find the value after specific cell then you can mention the cell in the After parameter of the Find syntax.

Recommended Articles

This has been a guide to VBA Find Function. Here we discussed VBA Find and how to use Excel VBA Find Function along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

All in One Software Development Bundle (600+ Courses, 50+ projects)

Ссылка на основную публикацию
Adblock
detector