VBA Range Method - Find

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.