Vba Find With Multiple Conditions
Locates and returns a Microsoft Outlook item object that satisfies the given Filter.
Syntax
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 find function is the range of cells. Like in which range we want to find a value. Keywords (or Categories) The Categories field is of type keywords, which is designed to hold multiple values. When accessing it programmatically, the Categories field behaves like a Text field, and the string must match exactly. Values in the text string are separated by a comma and a space. This typically means that you cannot use the Find and Restrict methods on a keywords field if it.
expression.Find (Filter)
expression An expression that returns a Items object.
Parameters
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| Filter | Required | String | A string that specifies the criteria that the returned object must satisfy. |
Return value
An Object value that represents an Outlook item if the call succeeds; returns Null (or Nothing in Visual Basic) if it fails.
Remarks
To use content indexing search in the Items collection, use the Restrict method. FindRow will return an error if Filter contains content indexing keywords. For more information on content indexing keywords, see Filtering Items Using Query Keywords.
The method will return an error with the following properties in the Filter :
| Body | LastFirstNoSpaceCompany |
|---|---|
| Children | LastFirstSpaceOnlyCompany |
| Class | LastFirstNoSpaceAndSuffix |
| Companies | MemberCount |
| CompanyLastFirstNoSpace | NetMeetingAlias |
| CompanyLastFirstSpaceOnly | NetMeetingAutoStart |
| ContactNames | NetMeetingOrganizerAlias |
| Contacts | NetMeetingServer |
| ConversationIndex | NetMeetingType |
| DLName | RecurrenceState |
| Email1EntryID | ReplyRecipients |
| Email2EntryID | ReceivedByEntryID |
| Email3EntryID | ReceivedOnBehalfOfEntryID |
| EntryID | ResponseState |
| HTMLBody | Saved |
| IsOnlineMeeting | Sent |
| LastFirstAndSuffix | Submitted |
| LastFirstNoSpace | VotingOptions |
| AutoResolvedWinner | DownloadState |
| BodyFormat | IsConflict |
| InternetCodePage | MeetingWorkspaceURL |
| Permission |
Creating Filters for the Find and Restrict Methods
The syntax for the filter varies depending on the type of field you are filtering on.
String (for Text fields)
When filtering text fields, you can use either a pair of single quotes (') or a pair of double quotes (') to delimit the values that are part of the filter. For example, all of the following lines function correctly when the field is of type String :
In specifying a filter in a Jet or DASL query, if you use a pair of single quotes to delimit a string that is part of the filter, and the string contains another single quote or apostrophe, then add a single quote as an escape character before the single quote or apostrophe. Use a similar approach if you use a pair of double quotes to delimit a string. If the string contains a double quote, then add a double quote as an escape character before the double quote.
For example, in the DASL filter string that filters for the Subject property being equal to the word can't, the entire filter string is delimited by a pair of double quotes, and the embedded string can't is delimited by a pair of single quotes. There are three characters that you need to escape in this filter string: the starting double quote and the ending double quote for the property reference of http://schemas.microsoft.com/mapi/proptag/0x0037001f, and the apostrophe in the value condition for the word can't. Applying the appropriate escape characters, you can express the filter string as follows:
Alternatively, you can use the chr(34) function to represent the double quote (whose ASCII character value is 34) that is used as an escape character. Using the chr(34) substitution for a double-quote escape character, you can express the last example as follows:
Escaping single and double quote characters is also required for DASL queries with the ci_startswith or ci_phrasematch operators. For example, the following query performs a phrase match query for can't in the message subject:
Another example is a DASL filter string that filters for the Subject property being equal to the words the right stuff, where the word stuff is enclosed by double quotes. In this case, you must escape the enclosing double quotes as follows:
A different set of escaping rules apply to a property reference for named properties that contain the space, single quote, double quote, or percent character. For more information, see Referencing Properties by Namespace.
Date
Although dates and times are typically stored with a Date format, the Find and Restrict methods require that the date and time be converted to a string representation. To make sure that the date is formatted as Outlook expects, use the Format function. The following example creates a filter to find all contacts that have been modified after January 15, 1999 at 3:30 P.M.
Boolean Operators
Boolean operators, TRUE/ FALSE, YES/NO, ON/OFF, and so on, should not be converted to a string. For example, to determine whether journaling is enabled for contacts, you can use this filter:
Note
If you use quotation marks as delimiters with Boolean fields, then an empty string will find items whose fields are False and all non-empty strings will find items whose fields are True.
Keywords (or Categories)
The Categories field is of type keywords, which is designed to hold multiple values. When accessing it programmatically, the Categories field behaves like a Text field, and the string must match exactly. Values in the text string are separated by a comma and a space. This typically means that you cannot use the Find and Restrict methods on a keywords field if it contains more than one value. For example, if you have one contact in the Business category and one contact in the Business and Social categories, you cannot easily use the Find and Restrict methods to retrieve all items that are in the Business category. Instead, you can loop through all contacts in the folder and use the Instr function to test whether the string 'Business' is contained within the entire keywords field.
Note
A possible exception is if you limit the Categories field to two, or a low number of values. Then you can use the Find and Restrict methods with the OR logical operator to retrieve all Business contacts. For example (in pseudocode): 'Business' OR 'Business, Personal' OR 'Personal, Business.' Category strings are not case-sensitive.
Integer
You can search for Integer fields with or without quotation marks as delimiters. The following filters will find contacts that were created with Outlook 2000:
Using Variables as Part of the Filter
As the Restrict method example illustrates, you can use values from variables as part of the filter. The following Microsoft Visual Basic Scripting Edition (VBScript) code sample illustrates syntax that uses variables as part of the filter.
This approach uses Chr(34) to delimit the value:
This approach uses double quotation marks to delimit the value:
Using Logical Operators as Part of the Filter
Logical operators that are allowed are AND, OR, and NOT. The following are variations of the clause for the Restrict method, so you can specify multiple criteria.
OR: The following code returns all contact items that have either Business or Personal as their category.
AND: The following code retrieves all personal contacts who work at Microsoft.
NOT: The following code retrieves all personal contacts who don't work at Microsoft.
Additional Notes
If you are trying to use the Find or Restrict methods with user-defined fields, the fields must be defined in the folder, otherwise an error will occur. There is no way to perform a 'contains' operation. For example, you cannot use Find or Restrict to search for items that have a particular word in the Subject field. Instead, you can use the AdvancedSearch method, or you can loop through all of the items in the folder and use the InStr function to perform a search within a field. You can use the Restrict method to search for items that begin within a certain range of characters. For example, to search for all contacts with a last name beginning with the letter M, use this filter:
See also
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
-->Finds specific information in a range.
Note
Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.
Syntax
expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
expression A variable that represents a Range object.
Parameters
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| What | Required | Variant | The data to search for. Can be a string or any Microsoft Excel data type. |
| After | Optional | Variant | The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If you do not specify this argument, the search starts after the cell in the upper-left corner of the range. |
| LookIn | Optional | Variant | Can be one of the following XlFindLookIn constants: xlFormulas, xlValues, xlComments, or xlCommentsThreaded. |
| LookAt | Optional | Variant | Can be one of the following XlLookAt constants: xlWhole or xlPart. |
| SearchOrder | Optional | Variant | Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns. |
| SearchDirection | Optional | XlSearchDirection | The search direction. |
| MatchCase | Optional | Variant | True to make the search case-sensitive. The default value is False. |
| MatchByte | Optional | Variant | Used only if you have selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents. |
| SearchFormat | Optional | Variant | The search format. |
Return value
A Range object that represents the first cell where that information is found.
Remarks
This method returns Nothing if no match is found. The Find method does not affect the selection or the active cell.
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.
You can use the FindNext and FindPrevious methods to repeat the search.
When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.
To find cells that match more complicated patterns, use a For Each..Next statement with the Like operator. For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters Cour. Telepace studio crack. When Microsoft Excel finds a match, it changes the font to Times New Roman.
Example
This example finds all cells in the range A1:A500 on worksheet one that contain the value 2, and changes it to 5.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.