Find Method in Excel VBA, Find Multiple Occurrences, Use Find Method to do Vlookup, Find Method to Search Date
————————————————————————
Contents:
Find multiple occurrences of an item or value in a range
Using Find Method to do VLookUp
Using Excel Find Method to Search for a Date
————————————————————————
To search for a specific item or value in a range, use the Find Method which returns the Range, ie. the first cell, where the item or value is found. If no match (ie. matching cell) is found, it returns Nothing.
Syntax of Range.Find Method:
RangeObject.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
Only the What argument is necessary to specify, all other arguments are optional.
RangeObject represents a range in which the specific item or value is searched. You can search within specific cells viz. Range(“B1:B100”), Columns(1), … or cover all cells of a worksheet: ActiveSheet.Cells, and so on.
What: Is the item or value which is searched. Can be any data type viz. date, string, integer, …
After: This represents a single cell which you will specify, AFTER which the search begins. Because the search starts AFTER this cell, the specified cell is searched right at the end. When the search starts after the specified cell and reaches the end of the search range, without finding the search value, the search starts again from the beginning of the search range till the specified cell. If the argument is not specified, it is the cell in the upper-left corner of the search range AFTER which the search starts.
If you specify After:=Range(“A13”) wherein the search range is Range(“A1:A20”), the find method will begin the search from Range(“A14”), search till Range(“A20”) and then search from Range(“A1”) till Range(“A13”).
LookIn: This arguments specifies the type of information – can be xlValues or xlFormulas or xlComments which indicate whether to search in the cell value, cell formula or cell comments. Default value is xlFormulas.
Take the case in which cell A12 contains the formula “=SUM(A4,A5)” whose total 57 appears in the cell and cell A17 contains the absolute value 57 mentioned therein: the find method -> ActiveSheet.Range(“A1:A100”).Find(What:=“57”, After:=ActiveSheet.Range(“A1”), LookIn:=xlFormulas) will return $A$17. This is because the value 57 does not appear in the formula “=SUM(A4,A5)” entered in cell A12 while it appears in the formula bar in cell A17; the find method -> ActiveSheet.Range(“A1:A100”).Find(What:=“57”, After:=ActiveSheet.Range(“A1”), LookIn:=xlValues) will return $A$12 because the value 57 appears first in cell A12 and only after this in cell A17. Similarly, in the case where cell A12 contains the formula “=SUM(A4,A5)” whose total 57 appears in the cell and cell A14 contains the string “SUM”: the find method -> ActiveSheet.Range(“A1:A100”).Find(What:=“sum”, After:=ActiveSheet.Range(“A1”), LookIn:=xlValues) will return $A$14; whereas find method -> ActiveSheet.Range(“A1:A100”).Find(What:=“sum”, After:=ActiveSheet.Range(“A1”), LookIn:=xlFormulas) will return $A$12.
See below code which illustrates this:
Example – Find method options of LookIn:=xlFormulas vs LookIn:=xlValues
Sub FindMethodLookin()
‘compare find method options LookIn:=xlFormulas vs LookIn:=xlValues
Dim rngFindValue As Range
‘————
‘returns $A$17
Set rngFindValue = ActiveSheet.Range(“A1:A100”).Find(What:=“57”, After:=ActiveSheet.Range(“A1”), LookIn:=xlFormulas)
If Not rngFindValue Is Nothing Then
MsgBox rngFindValue.Address
End If
‘————
‘returns $A$12
Set rngFindValue = ActiveSheet.Range(“A1:A100”).Find(What:=“57”, After:=ActiveSheet.Range(“A1”), LookIn:=xlValues)
If Not rngFindValue Is Nothing Then
MsgBox rngFindValue.Address
End If
‘————
‘returns $A$14
Set rngFindValue = ActiveSheet.Range(“A1:A100”).Find(What:=“sum”, After:=ActiveSheet.Range(“A1”), LookIn:=xlValues)
If Not rngFindValue Is Nothing Then
MsgBox rngFindValue.Address
End If
‘————
‘returns $A$12
Set rngFindValue = ActiveSheet.Range(“A1:A100”).Find(What:=“sum”, After:=ActiveSheet.Range(“A1”), LookIn:=xlFormulas)
If Not rngFindValue Is Nothing Then
MsgBox rngFindValue.Address
End If
End Sub
LookAt: You can specify xlWhole or xlPart, if you want an exact match or a partial match respectively. Default value is xlPart. A search for “Humpty” will return the cell which has “Humpty Dumpty”, using xlPart, because there is a partial match. Use xlWhole to match the entire value or string ie. to exactly match the value in a cell.
SearchOrder: You can specify xlByRows or xlByColumns for this argument, which indicate whether to search by rows or search by columns respectively. Default value is xlByRows.
Take the case in which cells A7 and B3 contain the string “sum” therein: the find method -> ActiveSheet.Range(“A1:B100”).Find(What:=“sum”, LookIn:=xlValues, SearchOrder:=xlByRows) will return $B$3, whereas the find method -> ActiveSheet.Range(“A1:B100”).Find(What:=“sum”, LookIn:=xlValues, SearchOrder:=xlByColumns) will return $A$7.
XlSearchDirection: You can specify xlNext or xlPrevious – SearchDirection xlNext searches downwards (ie. the next matching value) and xlPrevious looks upwards or backwards (ie. the previous matching value) in the search range. Default value is xlNext. If you specify After:=Range(“A13”) wherein the search range is Range(“A1:A20”) and set your SearchDirection:=xlNext, then the find function will begin searching from Range(“A14”) till Range(“A20”) and then search from Range(“A1”) till Range(“A13”).
MatchCase: Specify True for a case-sensitive search. The default value is False.
MatchByte: This argument can be used only if double-byte language support is selected or installed. To have double-byte characters match only double-byte characters specify True, while the False setting will match double-byte characters to their single-byte equivalents.
SearchFormat: Indicates if you want to search for specified formatting (True) or not (False). Default is False. Specify the format by using the FindFormat property of the Application object, and set SearchFormat argument to True. See below example which illustrates this argument.
Example – use the find method to search the first occurrence of string value “sum” which is in Bold font.
Sub FindMethodSearchFormat()
‘use the find method to search the first occurrence of string value “sum” which is in Bold font.
‘Note that the Find method will begin search AFTER cell A1 (ie. from cell A2) in absence of After argument.
Dim rngSearch As Range, rngLast As Range, rngFindValue As Range
‘set the search range:
Set rngSearch = ActiveSheet.Range(“A1:A100”)
‘specify last cell in range:
Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)
‘specify the format by using the FindFormat property of the Application object:
Application.FindFormat.Font.FontStyle = “Bold”
Set rngFindValue = rngSearch.Find(What:=“sum”, After:=rngLast, LookIn:=xlValues, SearchFormat:=True)
MsgBox rngFindValue.Address
End Sub
Explicitly specify arguments each time you use the Find method:
Every time the Find method is used, the settings for LookIn, LookAt, SearchOrder, and MatchByte get saved. Unless the values for these arguments are specified again, the previously saved values are used again the next time the method is called. Hence It is important to set these arguments explicitly each time this method is used. In this manner, the previously used argument value becomes the default if not specified in the argument’s subsequent use. Hence the default value changes on each use for the arguments LookIn, LookAt, SearchOrder, and MatchByte. For example, if you specify LookIn argument of xlFormulas, then xlFormulas becomes the default for the LookIn argument. And in subsequent use if you omit to mention the LookIn argument, it will default to xlFormulas.
Find Method is Quicker than Loop:
In case you want to search an item or value in a range, a common practice is to use the Loop method viz. For … Next Loop. In this case Excel searches each cell to look for the specified item or value. If the search Range is large, this could take a long time and the Loop method will be quite slow. An important benefit in using the Find method is its speed, as it is quicker than loop.
Find multiple occurrences of an item or value in a range
To repeat the search ie. to find all (multiple) occurrences of an item or value in a range, use FindNext or FindPrevious methods. These methods are used to continue the search started with the Find method, using the same parameters or conditions, and return the next (FindNext method) or previous (FindPrevious method) matching cell.
————————–
Range.FindNext Method
Syntax: RangeObject.FindNext(After)
RangeObject represents a range in which the specific item or value is searched.
After: This is the cell (a single cell) you specify AFTER which the search starts. Because the search starts AFTER this cell, the specified cell is searched right at the end. When the search starts after the specified cell and reaches the end of the search range, without finding the search value, the search starts again from the beginning of the search range till the specified cell. It is optional to specify this argument, and if not specified, it is the cell in the upper-left corner of the search range AFTER which the search starts.
————————–
Range.FindPrevious Method
Syntax: RangeObject.FindPrevious(After)
RangeObject represents a range in which the specific item or value is searched.
After: This is the cell (a single cell) you specify BEFORE which the search starts. Because the search starts BEFORE this cell, the specified cell is searched right at the end. It is optional to specify this argument, and if not specified, it is the cell in the upper-left corner of the search range BEFORE which the search starts.
————————–
Stop the search from going into a loop: When the search reaches the end of the search range, it loops and the search starts again from the beginning of the range. To stop the search from again going into a loop, save the first cell address to check it against each subsequent cell address, which find the searched item or value. Refer below example which illustrates finding multiple ocurrences in a range.
Example – finding multiple occurrences of a value in a range.
Sub FindMultipleOccurrences()
‘finding multiple occurrences of a value in a range – find the string “OldItem” in a search range, and replace with string “NewItem” and change its font color.
Dim rngSearch As Range, rngLast As Range, rngFound As Range
Dim strFirstAddress As String
‘set the search range:
Set rngSearch = ActiveSheet.Range(“A1:A100”)
‘specify last cell in range:
Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)
‘Find the string “OldItem” in search range, when it first occurrs. Note that the After argument is used to begin search after the last cell in the search range.
Set rngFound = rngSearch.Find(What:=“OldItem”, After:=rngLast, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
‘if “OldItem” is found in search range:
If Not rngFound Is Nothing Then
‘saves the address of the first occurrence of “OldItem”, in the strFirstAddress variable:
strFirstAddress = rngFound.Address
Do
‘Find next occurrence of “OldItem”. Note, that we do not start from the first occurrence of “OldItem” (ie. strFirstAddress).
Set rngFound = rngSearch.FindNext(rngFound)
‘replace “OldItem” with “NewItem”:
rngFound.Value = “NewItem”
‘font color is changed:
rngFound.Font.Color = vbRed
‘The Loop ends on reaching the first occurrence of “OldItem” (ie. strFirstAddress). We have retained the value of “OldItem” till this step because if in the first occurrence, “OldItem” had been replaced by “NewItem”, this step would
give an error.
Loop Until rngFound.Address = strFirstAddress
End If
End Sub
Using Find Method to do VLookUp
Example – With reference to Image 1, below code shows how to use “Find Method” and “Offset Property” to do a VLookUp.
Sub FindVLookup()
‘using Find Method to do a VLookUp: For each student in column A, find student name in column E, and place his marks in column B, only if he is in Class IX – refer Image 1.
Dim rngSearch As Range, rngStudentNames As Range, rngFound As Range, rngStudent As Range
Set rngSearch = ActiveSheet.Range(“E3:E7”)
Set rngStudentNames = ActiveSheet.Range(“A3:A7”)
‘searches for all student names mentioned in cells A3 to cells A7:
For Each rngStudent In rngStudentNames
‘student names are searched in Range(“E3:E7”):
Set rngFound = rngSearch.Find(What:=rngStudent, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
‘if student name is found AND if student is in class IX:
If Not rngFound Is Nothing And rngFound.Offset(0, 1) = “IX” Then
‘found student’s marks are entered in column B, against his name given in column A:
rngStudent.Offset(0, 1) = rngFound.Offset(0, 2)
End If
Next
End Sub
Using Excel Find Method to Search for a Date
Excel Dates Equate to Serial Numbers:
Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system. In this system, the serial number 1 represents 1/1/1900 12:00:00 AM, the first supported day from when the Excel calendar starts. Bug in Excel: Excel erroneously treats 1900 as a leap year, which has presumably been done knowingly by Microsoft to provide compatibility with Lotus 1-2-3, and so actually the bug would have been in Lotus 123 (Excel’s predecessor).
In Excel, Dates equate to a “serial number” (which is a numeric value) that is the count of number of days elapsed from a certain referenced date. The integer part (values to the left of decimal) is the number of days elapsed since January 1, 1900. For example, January 1, 1900 is stored as 1; January 2, 1900 is stored as 2; March 15, 2001 is stored as 36,965. The fractional part (values to the right of decimal) holds time information, and represents the time as a fraction of a whole day. For example, 12.00AM (midnight) is stored as 0; 6:00AM is stored as 0.25; 12.00PM (noon) is stored as 0.5; 6:00PM is stored as 0.75; 6:00:30PM is stored as 0.750347222. To check the “serial number” of a date and time simply format the cell as “General”. The Date and Time of “10/3/1954 6:00:00 AM” has a serial number of 20000.25..
Using a Valid Excel Date and Format:
Using the Find Method to Find or Search a Date can be tricky. The date format should correspond to the default date format as set in your desktop/Windows which, unless specifically changed, should be in its standard format of “Short Date” or “Long Date’, viz. “1/22/2010” or “January 22, 2010”. It does not matter in which date format it is displayed in the worksheet, only it should be a valid Excel date equating to a valid serial number.
The following code shows how to use Find Method to Search for a Date
Example – Search for a date within a range – refer Image 2.
Sub FindMethod_SearchDate()
‘Search for a date within a range – refer Image 2.
‘user enters the date he wants to find in Range (“D2”) – he enters the serial number 41200 in this cell with “General” formatting;
‘user wants to find the date in search Range (“A1:A100”) – the range “$A$5” is returned in this example because the serial number 41200 corresponds to the date 18-Oct-12 which is entered in cell A5.
Dim rngFound As Range, rngSearch As Range, rngLast As Range
Dim strDate As String
‘search range to find the date:
Set rngSearch = ActiveSheet.Range(“A1:A100”)
Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)
‘user enters the date he wants to find in Range: ActiveSheet.Range(“D2”).
‘Format(“7/18/10”, “Short Date”) returns “7/18/2010”; Format(“7/18/10”, “Long Date”) returns “Sunday, July 18, 2010”.
‘Format Function in vba: Display a date according to your system’s short date format using “Short Date”; and using “Long Date” displays a date according to your system’s long date format.
strDate = Format(ActiveSheet.Range(“D2”), “Short Date”)
‘The IsDate function [syntax: IsDate(expression)] returns True if the expression is a valid date, else it returns False.
If IsDate(strDate) = False Then
MsgBox “Incorrect Date Format”
Exit Sub
End If
‘CDate converts a number or text string to a Date data type. CDate(40200) returns “1/22/2010”; CDate(“October 15, 2009”) returns “10/15/2009”; CDate(“2:25:15 PM”) returns “2:25:15 PM”; CDate(“hello”) returns an error.
Set rngFound = rngSearch.Find(What:=CDate(strDate), After:=rngLast, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not rngFound Is Nothing Then
‘return range address ($A$5 – in this example) if date is found:
MsgBox rngFound.Address
Else
‘if date is not found in search range:
MsgBox “Date Not Found”
End If
End Sub