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:
|
| LookAt | Поиск частичного или полного совпадения. Список констант xlLookAt:
|
| SearchOrder | Определяет способ поиска. Список констант xlSearchOrder:
|
| SearchDirection | Определяет направление поиска. Список констант xlSearchDirection:
|
| MatchCase | Определяет учет регистра:
|
| MatchByte | Условия поиска при использовании двухбайтовых кодировок:
|
| SearchFormat | Формат поиска – используется вместе со свойством Application.FindFormat. |
* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes .
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.
В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.
Знаки подстановки для поисковой фразы
Условные знаки в шаблоне поисковой фразы:
- ? – знак вопроса обозначает любой отдельный символ;
- * – звездочка обозначает любое количество любых символов, в том числе ноль символов;
Простые примеры
При использовании метода Range.Find в VBA Excel необходимо учитывать следующие нюансы:
- Так как этот метод возвращает объект Range (в виде одной ячейки), присвоить его можно только объектной переменной, объявленной как Variant, Object или Range, при помощи оператора Set.
- Если поисковая фраза в заданном диапазоне найдена не будет, метод Range.Find возвратит значение Nothing. Обращение к свойствам несуществующей ячейки будет генерировать ошибки. Поэтому, перед использованием результатов поиска, необходимо проверить объектную переменную на содержание в ней значения Nothing.
В примерах используются переменные:
- myPhrase – переменная для записи поисковой фразы;
- myCell – переменная, которой присваивается первая найденная ячейка, содержащая поисковую фразу, или значение Nothing, если поисковая фраза не найдена.
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)
Ron de Bruin
Excel Automation
Find value in Range, Sheet or Sheets with VBA
Copy the code in a Standard module of your workbook, if you just started with VBA see this page.
Where do I paste the code that I find on the internet
Find is a very powerful option in Excel and is very useful. Together with the Offset function you can also change cells around the found cell. Below are a few basic examples that you can use to in your own code.
Use Find to select a cell
The examples below will search in column A of a sheet named «Sheet1» for the inputbox value. Change the sheet name or range in the code to your sheet/range.
Tip: You can replace the inputbox with a string or a reference to a cell like this
FindString = «SearchWord»
Or
FindString = Sheets(«Sheet1»).Range(«D1»).Value
This example will select the first cell in the range with the InputBox value.
If you have more then one occurrence of the value this will select the last occurrence.
If you have date’s in column A then this example will select the cell with today’s date. Note : If your dates are formulas it is possible that you must change xlFormulas to xlValues in the example below. If your dates are values xlValues is not always working with some date formats.
Mark cells with the same value in column A in the B column
This example search in Sheets(«Sheet1») in column A for every cell with «ron» and use Offset to mark the cell in the column to the right. Note: you can add more values to the array MyArr.
Color cells with the same value in a Range, worksheet or all worksheets
This example color all cells in the range Sheets(«Sheet1»).Range(«B1:D100») with «ron». See the comments in the code if you want to use all cells on the worksheet. I use the color index in this example to give all cells with «ron» the color 3 (normal this is red)
Tip: For changing the Font color see the example lines below the macros.
Example for all worksheets in the workbook
Change the Font color instead of the Interior color
‘Change the fill color to «no fill» in all cells
.Interior.ColorIndex = xlColorIndexNone
‘Change the font in the column to Automatic
.Font.ColorIndex = 0
Rng.Interior.ColorIndex = myColor(I)
With
Rng.Font.ColorIndex = myColor(I)
Copy cells to another sheet with Find
The example below will copy all cells with a E-Mail Address in the range Sheets(«Sheet1»).Range(«A1:E100») to a new worksheet in your workbook. Note: I use xlPart in the code instead of xlWhole to find each cell with a @ character.
If you only want to replace values in your worksheet then you can use Replace manual (Ctrl+h) or use Replace in VBA. The code below replace ron for dave in the whole worksheet. Change xlPart to xlWhole if you only want to replace cells with only ron.
Excel VBA FIND Function (& how to handle if value NOT found)
Doing a CTRL + F on Excel to find a partial or exact match in the cell values, formulas or comments gives you a result almost instantly. In fact, it might even be faster to use this instead looping through multiple cells or rows in VBA. MS Excel’s FIND method automates this process without looping.
Arguments needed
MSDN.Microsoft.com gives you a summary of parameters and arguments for MS Excel functions.
This means that the FIND function can be used on a Range object on the worksheet. It is used in the format:
Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
The answer should be saved as a Range object, so the Set keyword should be used when using the FIND() method.
The only required parameter is what is being looked for and the rest is optional. The optional parameters have default values corresponding to whatever was selected last on a manual search. For example, if the user specified to search within the Excel comments in the last manual search, the FIND() function will then only look at Excel comments if a LookIn value is not specified—which may or may not be how the FIND() function is expected to run. Because of this, it’s recommended to specify the parameters listed below to make sure the function runs in a way that is expected:
- LookIn – decides where the variable is to be found (xlFormulas, xlValues, xlNotes)
- LookAt – full or partial match (xlWhole, or xlPart)
- MatchCase – TRUE to make the search case sensitive. Default value is FALSE
- After – useful when looking for multiple matches since it specifies the cell after which the search should begin
The FIND() method to find one match
In this example, the button One Match should display the corresponding article code from the given data table depending on what company code the user selects.
- Bring up the Visual Basic editor (ALT + F11).
- Create a new module by going to Insert > Module.
- Create a new Subprocedure
Find the company id match
1. Write the variable to keep the result of the FIND() function. In this example, the variable CompId is used and is written as:
Dim CompId As Range
- What:=Range(“B3”).Value – the value to be searched
- LookIn:=xlValues – looks at the cell values
- LookAt:=xlWhole – full match
Note: cells.Find can be used if you want to search the entire worksheet or don’t know where the company Id column is.
Activate the watch window
Activating the Watch Window (under View) helps you identify what steps are missing in the code. Highlight the variable to be watched and drag it to the watch window. Run the code (or press F8).
The watch window shows the value of CompId, which is the match of the Company Id selected by the user. To better understand exactly which cell it is pointing to, change the Expression from CompId to CompId.Address. This will then give you $A$8, which corresponds to the address of the match.
Display the equivalent Article Code
Since the corresponding Article Code should be displayed in cell C3, set up the formula to get the Article code which is 4 columns to the right of the company id on the data table using the Offset function:
This now becomes:
Note: A 0 can be used for the [RowOffset] parameter, or left blank and skipped by the symbol “,”
Address values that are not found on the table
When the selected Company Id is not found on the table, the watch window shows an error in CompId.Address because the CompId.Value is nothing and is invalid for the Range data type.
An IF statement should be added to address such cases. In VBA, the keyword NOT is used often since it is usually easier to specify what something isn’t, than what something is. In this case, the result can either be nothing or specific ranges. When it is nothing, you can alert the user with a message box indicating so. The IF statement then becomes:
If Not CompId is Nothing Then
Msgbox “Company not found!”
However, while it displays the message box when the company id is not found, the value in cell C3 retains the result of the previous search. Make sure that the macro deletes the contents of the cell before it runs the rest of the code:
Assign the macro to the button
Right click on the button > Assign macro. Select the macro name.