Welcome to Excel Avon
Find Function in Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
Today’s article is about Find function in Excel using VBA, today we are going to understand how to sort data on Excel VBA, VBA Excel’s FIND function searches for a given set of values in a specified range. It looks for the first instance of such a value and if a match is found, the function returns the cell that contains it. However, if no match is found, the function returns nothing. VBA’s FIND function can return an exact or partial match.
Let me introduce you to the syntax of find function
EXPRESSION: Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
Arguments –
The “expression” is the range object, which precedes the FIND function in a VBA code. The search range can be one or more rows, columns or the entire worksheet.
What – This is the value to be searched. It can be numeric, textual or any other data type of Excel.
After – This indicates the cell after which the search will begin.
LookIn – This is the place (or data) where the value needs to be searched. It can be a comment (xlComments), formula (xlFormulas) or value (xlValues). Default value is xlFormulas.
LookAt – Parameter decides whether to match the entire cell contents or a portion of the cell contents. The constants are xlWhole and xlPart for exact and partial matches, respectively.
SearchOrder – This suggests the order of the search. One can specify whether the search will be in rows (xlByRows) or columns (xlByColumns)
SearchDirection – This indicates the direction in which the search will be carried out. One can search downwards or in the next cell with the constant xlNext.
MatchCase – This decides whether the search is case-sensitive or not. If the search is case-sensitive, this argument is specified as true, otherwise it is false.
MatchByte – This is used if one has installed or selected double-byte language support. It must be specified as true,
SearchFormat – This indicates whether the value to be searched should be in a specific format or not. The default value false.
Note: Only the argument “what” is required. The rest of the arguments are optional.
How to use Find Function in Excel VBA
As you can see, we have just explained the syntax, now we will understand the find function through the example. We have to go like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Once we insert the module, we will write a subroutine to Find Function in Module.
Sub UseFindFunction() End Sub
And then Define variable for Range and Worksheet, and value String as Variant
Sub UseFindFunction() Dim FCell As Range Dim WS As Worksheet Dim vStr As Variant End Sub
Now we will store the value in vstr, the value that will be stored in vstr should be in data. You can see in the image below that the value is given in the data.
Sub UseFindFunction() Dim FCell As Range Dim WS As Worksheet Dim vStr As Variant Vstr = "Aroos Reason" End Sub
Set the worksheet as activesheet
Sub UseFindFunction() Dim FCell As Range Dim WS As Worksheet Dim vStr As Variant vStr = "Aroos Reason" Set WS = ACtivesheet End Sub
Now we will Fcell as range so writing range with ws.range, write find function as well as give value to the first parameter of the function, after this we will use LookAt parameter
Sub UseFindFunction()
Dim FCell As Range
Dim WS As Worksheet
Dim vStr As Variant
vStr = "Aroos Reason"
Set WS = ACtivesheet
Set FCell = WS.Range("A:A").Find(What:=vStr,LookAt:=xlWhole)
End Sub
Now we have made a condition that if the given value is found, so we will get a message ‘Value found in cell’ with cell address. OR else it will show the message as “Value did not find”. With Exclamation sign.
Sub UseFindFunction()
Dim FCell As Range
Dim WS As Worksheet
Dim vStr As Variant
vStr = "Aroos Reason"
Set WS = ACtivesheet
Set FCell = WS.Range("A:A").Find(What:=vStr,LookAt:=xlWhole)
If Not FCell Is Nothing Then
MsgBox "Value found in cell " & FCell.Address
Else
MsgBox "Valud did not find!", vbExclamation
End If
End Sub
Now run the code
As we found the given value in the A8 range
If a value is twice, then how will it be funded, let’s also understand this. For this we have to use after parameter and in after we have to write the cell of first found value.
Sub UseFindFunction()
Dim FCell As Range
Dim WS As Worksheet
Dim vStr As Variant
vStr = "Aroos Reason"
Set WS = ACtivesheet
Set FCell = WS.Range("A:A").Find(What:=vStr, After:=WS.Range("A8"), LookAt:=xlWhole)
If Not FCell Is Nothing Then
MsgBox "Value found in cell " & FCell.Address
Else
MsgBox "Valud did not find!", vbExclamation
End If
End Sub
After click run button we will go to the worksheet, after cell A8, it will show the range in which the value is found, here the value is found in cell A15.
Now we will remove the After parameter and use the MatchCase parameter, Content is case sensitive or not. True or False. we will slightly change the case of the stored value.
Sub UseFindFunction()
Dim FCell As Range
Dim WS As Worksheet
Dim vStr As Variant
vStr = "Aroos reason"
Set WS = ACtivesheet
Set FCell = WS.Range("A:A").Find(What:=vStr, LookAt:=xlWhole, MatchCase:=True)
If Not FCell Is Nothing Then
MsgBox "Value found in cell " & FCell.Address
Else
MsgBox "Valud did not find!", vbExclamation
End If
End Sub
Click run button
After pressing the click button, Match case is true, if the case is not matched, the message ‘Value did not find’ will be received.
We can make the value case same or else we will make the MatchCase false then we will get the value then we will store the value in same case.
Sub UseFindFunction()
Dim FCell As Range
Dim WS As Worksheet
Dim vStr As Variant
vStr = "Aroos Reason"
Set WS = ACtivesheet
Set FCell = WS.Range("A:A").Find(What:=vStr, LookAt:=xlWhole, MatchCase:=True)
If Not FCell Is Nothing Then
MsgBox "Value found in cell " & FCell.Address
Else
MsgBox "Valud did not find!", vbExclamation
End If
End Sub
Now after doing same case, we will click run button then we will get value found.
Remove the MatchCase parameter in Find function. Now we will store date in string value, now we will change the date format by going to the data and also change the range because date is in B column so we will write range B
Sub UseFindFunction()
Dim FCell As Range
Dim WS As Worksheet
Dim vStr As Variant
vStr = DateSerial(2000, 12, 16)
Set WS = ACtivesheet
Set FCell = WS.Range("B:B").Find(What:=vStr, LookAt:=xlWhole)
If Not FCell Is Nothing Then
MsgBox "Value found in cell " & FCell.Address
Else
MsgBox "Valud did not find!", vbExclamation
End If
End Sub
Now we will change the date format by going to the data
After writing the code, we will click on the run button, we have a message like this “value didn’t find” message while stored value and range are all correct.
We will use LookIn parameter of find function, Parameters are xlFormulas, xlValues, xlComments. The format in which the data is stored is storing the same date format in the string value.
Sub UseFindFunction()
Dim FCell As Range
Dim WS As Worksheet
Dim vStr As Variant
vStr = "December 16, 2000"
Set WS = ACtivesheet
Set FCell = WS.Range("B:B").Find(What:=vStr, LookIn:=xlValues, LookAt:=xlWhole)
If Not FCell Is Nothing Then
MsgBox "Value found in cell " & FCell.Address
Else
MsgBox "Valud did not find!", vbExclamation
End If
End Sub
After changing the date format in the code and adding the LookIn type, click the run button, we found value in cell B8.
Now we will store the date with Serial Date in the value, short format the date containing the data, then click the Run button.
Sub UseFindFunction()
Dim FCell As Range
Dim WS As Worksheet
Dim vStr As Variant
vStr = DateSerial(2000, 16, 12)
Set WS = ACtivesheet
Set FCell = WS.Range("B:B").Find(What:=vStr, LookIn:=xlValues, LookAt:=xlWhole)
If Not FCell Is Nothing Then
MsgBox "Value found in cell " & FCell.Address
Else
MsgBox "Valud did not find!", vbExclamation
End If
End Sub
Before running the code, we will change the date format, then click the run button we will find the value
So, I hope you have understood How to use Find Function in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to use Find Function in Excel VBA
