Find Function in Excel - VBA

Introduction

• Find is a very powerful option in Excel and is very useful. 

• This post describes Find Function in Excel - VBA

• Objective is to Find a String or Value in Excel Workbook/Worksheet

VBA - Function

• The below function finds the given string and returns the row number. 

• If you want the cell address to be return by the function, then use the below line of code  

          find = ActiveCell.Address

Function find(ByRef findString As String) As Integer   

    Dim Rng As Range

    ActiveSheet.Range("A1").Select

    With ActiveWorkbook.ActiveSheet.Range("A:B")

        Set Rng = .find(What:=findString, LookIn:=xlValues)

        If Not Rng Is Nothing Then

          Application.Goto Rng, True

          find = ActiveCell.Row

        Else

          find = 0
          MsgBox "Search String Not Found"

        End If

    End With

End Function

And here's how you call it in your Program:

Public Sub callingProgram()

    Dim rowNumber As Integer
    rowNumber = find("Allwyn")
 
End Sub

                                       !********************* Find it.? J ***************************!