Range find is to finds specific information in a range.
Syntax : Range.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
Returns : Not xxx Is Nothing(True)/Nothing(False)
Under find we have to specify What is Required and the rest is optional but for me LookIn and LookAt also importance.
LookIn:=xlFormulas, xlValues, xlComments, or xlCommentsThreaded
LookAt:=xlWhole or xlPart
If the information found then we can get the 1st location only base on search direction for example row and column index. If we need to find next location then we have to combined with FindNext.
Below example will loop keyword in activesheet at column B and find this keyword in reference sheet at column E and get additional information at B and G.
VBA Vode:
Option Explicit
Sub FindMyBlogLink()
Dim LstRow As Long
Dim i As Integer, j As Integer
Dim ActSht As Worksheet, RefSht As Worksheet
Dim FndRng As Range, FndKey As Range
Dim StrKey As String
'To start with active sheet
Set ActSht = ActiveSheet
'To check reference sheet available or not?
For j = 1 To Sheets.Count
If Sheets(j).Name = "mrvba" Then
Set RefSht = Sheets("mrvba")
End If
Next j
'If reference sheet exist then start searching for keyword
If Not RefSht Is Nothing Then
Set FndRng = RefSht.Columns("E:E")
LstRow = ActSht.Cells.Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row + 1
i = 2
Do
StrKey = ActSht.Range("B" & i)
Set FndKey = FndRng.Find(StrKey, LookAt:=xlWhole)
' Set FndKey = FndRng.Find(StrKey, LookAt:=xlPart)
If Not FndKey Is Nothing Then
ActSht.Range("C" & i) = RefSht.Cells(FndKey.Row, 2)
ActSht.Range("D" & i) = RefSht.Cells(FndKey.Row, 7)
End If
StrKey = ""
Set FndKey = Nothing
i = i + 1
Loop Until i = LstRow
'Exit with mesaage if reference sheet not exist.
Else
MsgBox "Sorry! Reference sheet name mrvba not found."
Exit Sub
End If
End Sub
VBA Vode: (Sample from Microsoft with FindNext)
Sub FindValue()
Dim c As Range
Dim firstAddress As String
'To find number 2 in replace with 5
With ActiveSheet.Range("A1:A500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Debug.Print c.Address
Debug.Print c.Row
Debug.Print c.Column
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
End Sub
Note:
By using range find is much more faster than loop entire sheet looking for keyword match.
Read more about
Excel.range.find, macro enabled excel, excel macro,
vba coding, vba code at below links.
Microsoft Reference-Excel.range.find
Other Reference-Find-and-replace-extensibility
Leave your comments if you have any request.
Practice makes perfect.
Thank You.
