Vba excel find примеры - Учим Эксель

VBA Find

By Jeevan A YJeevan A Y

VBA Find Function in Excel

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.

find window in excel

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.

Example 1-1

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.

VBA Find Example 1-3

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.

VBA Find Example 1-5

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.

Интересно почитать:  Импорт из xml в excel

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”.

VBA Find Example 2-1

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.

Error

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

MSG Box

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 –

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 the find function in macros, we need to learn first what is a find function in excel Find Function In Excel Find function in excel finds the location of a character or a substring in a text string. In other words it finds the occurrence of a text in another text, as it gives us the position, the output returned by this function is an integer. read more . 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.

VBA Find

When we click on this, we get two options;

One is simple to find,

We can see it also has a mode of options that 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,

VBA Find 3

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.

Expressions in macros are ranges defined, such as range 1 or range 2. What is a keyword for what we want to search for 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 the Find function, which are optional. The only mandatory field required is what is a value we are trying to search.

Интересно почитать:  Как в эксель вставить ссылку на сайт

Basically, VBA finds Excel has one required argument, which is What 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 the 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 a 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 excelDeveloper Tab In ExcelEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more in order to be able to write VBA Codes.

VBA Find Example 1-1

  • We start writing our code by writing the following code as shown below,
  • The sample is the function name given to the 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.

VBA Find Example 1-4

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

VBA Find Example 1-5

  • 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 the cell, after which reference, we want to search the data.

Let us write the code for the above data.

  • Always remember to enable the developer tab from options and then from customizing ribbon in excelCustomizing Ribbon In ExcelRibbons in Excel 2016 are designed to help you easily locate the command you want to use. Ribbons are organized into logical groups called Tabs, each of which has its own set of functions.read more 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.

Example 2-2

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

Example 2-3

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

Example 2-4

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

Example 2-5

  • Define what does the Find variable should have,

Example 2-6

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

Example 2-7

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

Example 2-8

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

Example 2-9

What the above code does is search the string after the cell A2 and return 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 a downloadable excel sheet. You may also have a look at other articles related to Excel VBA –

Как получить перечень файлов в Excel при помощи VBA

Специально для примера я сделал папку, в какой расположил несколько файлов разных типов и две подпапки. Вот так они смотрятся:

Папка с файлами и каталогами

Давайте разглядим несколько методов получения перечня файлов.

Метод 1. Внедрение функции Dir

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

Вот там код данной функции, который выводит на лист 1 список файлов.

Здесь мы проверяем существование папки при помощи доборной функции. Снова таки не используем посторониих библиотек, а используем туже функцию Dir.

Вот так смотрится итог работы функции. Программка записала перечень файлов на текущий лист Excel.

Вывод списка файлов на лист

Тут мы не делали проверку на тип файла и вывели все файлы которые у нас были.

Если же нужно отобрать лишь определенные типы файлов, к примеру Excel файлы, то в нашем коде нужно создать доп проверку:

Символ "*" значит хоть какой набор знаков. Таковым образом, мы учли разные версии файлов Excel (xls, xlsx, xlsm).

Метод 2. Используем объект FileSystemObject

В этом случае мы будем не попросту получать наименования файлов, но также определять тип файла, получать его размер и дату сотворения. Для этого нам будет нужно употреблять объект FileSystemObject. Он предоставляет нам сведения о файловой системе компа.

Но, чтоб начать его употреблять придется подключить одну библиотеку. Не пугайтесь, она есть на всех компах с Windows, поэтому закачивать ничего не придется. Чтоб подключить ее нужно:

  1. Открыть пункт меню Tools и избрать пункт References.
    Открыть пункт меню Tools и выбрать пункт References
  2. Избрать ссылку на библиотеку Microsoft Scripting Runtime.
    Выбрать ссылку на библиотеку Microsoft Scripting Runtime.
  3. Надавить Ок.

Сейчас перейдем к коду. Он мало упростился:

Направьте внимание на переменные. Переменная FSO — это новейший экземпляр объекта FileSystemObject. Здесь мы его объявляем и сходу создаем. Директива New весьма принципиальна, почти все здесь допускают ошибку. Также создаем объекты MyFolder и iFile — это тоже объекты FileSystemObject

Дальше делаем проверку на существование папки. В этом случае нам не нужна доборная функция , мы пользуемся способом FolderExists объекта (класса) FileSystemObject.

Ну и остается аналогично перебрать все файлы в директории. Здесь комфортно употреблять цикл For Each — Next.

Итог работы программки последующий:

Получаем список файлов с помощью FileSystemObject

Метод 3. Создаем функцию, которая возвращает файлы в папке и подпапках

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

Итак перейдем к коду:

Разберем главные моменты данной функции. На вход она воспринимает один неотклонимый аргумент — это путь к папке Path. Также может принимать два необязательных параметра:

  • Filter — перечисление перечня файлов, которые мы желаем получить. Перечислять нужно через запятую, к примеру "doc, xls*". Вы сможете употреблять знак "*" чтоб включить сопоставление по шаблону. По дефлоту фильтр отсутствует и ворачиваются все типы файлов.
  • Nesting — вложенность. Это наибольшее число вложенных папок в которые "проваливается" метод. По дефлоту равно 100.

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

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

Сейчас когда наша функция готова, просто используем ее где нам требуется вот так:

Функция нам возвращает коллекцию файлов в папке и подпапках. Вот так:

Список файлов в папке и вложенных папках

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

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