Improved Find Function for use in VBA

March 22nd, 2008 by admin admin

Below is the improved find function which aske users for area to find and also choose whether they would like the function to return the cell address, row or column of the cell returns by the find function

Function Find_Address(What_2_Find, Where_2_Find, _
GetAddress_Row_or_Column As String)

' this is a range.
Value_or_Formula = xlFormulas
    'xlVaues - search the text/value in the cell,
        'for cells with formula, it will look at the result.
    'xlformulas - search the text/value within formula
    'xlformulas works even when cell is hidden.
    'It is able to look for value too.
Exact_Partial = xlPart
    'xlPart - find cells that contains (What_2_Find)
    'xlWhole - find cells which contains exactly the value
        'placed in (What_2_Find)
Match_Capital_Letters = False
    'False - when A and a is treated the same
    ' True when A and a means different things.
With Where_2_Find
    Set c = .Find(What:=What_2_Find, LookIn:=Value_or_Formula, _
        LookAt:=Exact_Partial, MatchCase:=Match_Capital_Letters)
    'c is the cell that meet your find criteria
    If Not c Is Nothing Then
        Select Case GetAddress_Row_or_Column
            Case Is = "Address"
                Find_Address = c.Address
                    ' Change this to c.column if you want
                        'the column
                    ' where the text is found.
                    'change it to c.address if you want the
                        'address returned.
            Case Is = "Row"
                Find_Address = c.Row
            Case Is = "Column"
                Find_Address = c.Column
        End Select
        Find_Address = "A1" ' Cannot find, so default
                                'the address to A1
    End If
End With

End Function

Leave a Reply

You must be logged in to post a comment.