Some ways to use Find in VBA

When working with data in Excel, whether it's using VBA or functions, finding and referencing are two major tasks, the method of the Find series in VBA (Find, FindNext, range.findprevious) Returns a Range object that can be found and referenced at the same time, so it is useful to have some common find usages listed below:

Sub Find1 () ' Find in a column
Dim K
K = Range ("A:a"). Find ("A"). Row
MsgBox K
End Sub
==================================================
Sub Find11 () ' In Multi-column lookup
Dim K
K = Range ("A:b"). Find ("BCD"). Row
MsgBox K
End Sub
==================================================
Start position of Sub Find2 () ' Lookup
Dim K
K = Range ("A:b"). Find ("A", After:=range ("A5")). Row
MsgBox K
End Sub
==================================================
Sub Find3 () ' Find in value
Dim K
K = Range ("B:b"). Find ("SE", lookin:=xlvalues). Row
MsgBox K
End Sub
==================================================
Sub Find31 () ' Find in formula
Dim K
K = Range ("B:b"). Find ("C2", Lookin:=xlformulas). Address
MsgBox K
End Sub
==================================================
Sub Find32 () ' Find in Notes
Dim K
K = Range ("B:c"). Find ("AB", lookin:=xlcomments). Address
MsgBox K
End Sub
==================================================
Sub Find41 () ' Search by fuzzy
Dim K
K = Range ("B:c"). Find ("A", Lookin:=xlvalues, Lookat:=xlpart). Address
MsgBox K
End Sub
==================================================
Sub Find42 () ' Match lookup
Dim K
K = Range ("B:c"). Find ("A", Lookin:=xlvalues, Lookat:=xlwhole). Address
MsgBox K
End Sub
==================================================
Sub Find5 () ' Find by first column
Dim K
K = Range ("A:b"). Find ("AB", Lookin:=xlvalues, Lookat:=xlwhole, searchorder:=xlbyrows). Address
MsgBox K
End Sub
==================================================
Sub Find51 () ' Search by first column
Dim K
K = Range ("A:b"). Find ("AB", Lookin:=xlvalues, Lookat:=xlwhole, Searchorder:=xlbycolumns). Address
MsgBox K
End Sub
==================================================
Sub Find6 () ' Find direction (from rear forward)
Dim K
K = Range ("A:a"). Find ("A",, Xlvalues, Xlwhole, Xlbycolumns, xlprevious). Address
MsgBox K
End Sub
==================================================
Sub Find61 () ' Find direction (front-to-back)
Dim K
K = Range ("A:a"). Find ("A",, Xlvalues, Xlwhole, Xlbycolumns, Xlnext). Address
MsgBox K
End Sub
==================================================
Sub Find7 () ' Letter case
Dim K
K = Range ("A:b"). Find ("A",, Xlvalues, Xlwhole, Xlbycolumns, Xlnext, False). Address
MsgBox K
End Sub
==================================================
Sub F7 () ' cannot find the condition
Dim MRG as Range
Set MRG = Range ("A:a"). Find ("D")
If MRG is Nothing Then
MsgBox "Cannot find letter D"
Else
MsgBox "Find successful, cell address is:" & MRG. Address
End If
End Sub
==================================================
Sub F8 () ' Two-times find
Dim MRG as Range
Set MRG = Range ("A:a"). Find ("A")
Set mrg1 = Range ("A:a"). FindNext (MRG)
MsgBox Mrg1. Address
End Sub
==================================================
Sub F9 () ' Area lookup
Dim MRG as Range, AAA as String
Set MRG = Range ("A1:f16"). Find ("A")
AAA = MRG. Address
Do
Set MRG = Range ("A1:f16"). FindNext (MRG)
MsgBox MRG. Address
Loop Until MRG. Address = Aaaend Sub
==================================================
Sub Myfind ()
Dim Irange as range, ifined as range
Dim iStr, iaddress as String, N as Integer
' The above is the variable used to define
Set Irange = Range ("a2:a100") assigns a value to the Irange variable to the a2:a100 area
ISTR = Range ("A1"). Value ' assigns a value of A1 cell to the string variable to find
Set ifined = Irange.find (iStr, Lookat:=xlwhole) ' finds cells in the Irange area that are equal to the variable iStr and assigns you ifined variables, if you want to find cells that contain ISTR variables, Change parameter Lookat:=xlpart
If ifined is Nothing Then ' Judge ifined variable is empty
MsgBox "in" & Irange.address (0, 0) & area, no cells are found that equals & ISTR &! "
Exit Sub
Else
iaddress = ifined.address (0, 0)
Do
n = n + 1
Set ifined = Irange.findnext (ifined) ' continues to find cells that are equal to the ISTR variable
Loop while not ifined are nothing and iaddress <> ifined.address (0, 0) ' Do loop ' condition is ifined variable is non-null, and the cell address of the ifined variable does not equal the first one found Cell address
End If
MsgBox "in" & Irange.address (0, 0) & "area, Total found content equals" & iStr & "cells are:" & N & "!"
End Sub

Some ways to use Find in VBA