coding a VBA excel function to search a string in

Question

I am a rookie on excel... I am trying to create a function that takes a text string as parameter, trims it (ie removes the extra spaces at the end and at the beginning), searches for the first occurrence of the string in a range (on another spreadsheet), and returns the actual content of that cell.. I've written the code below but however I tweak it, it never returns anything!! Any help would be much appreciated !

Note: online I've found several examples of "subs" that do similar things, but when I try to convert them to a "function", they never work...

Public Function Find_First2(FindString As String) As String

    Dim Rng As Range

    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

            If Not Rng Is Nothing Then
                Find_First2 = Rng.Value
            Else
                Find_First2 = ""
            End If
        End With
    End If

End Function

Answer 1

You verify that trimming won't empty the whole string but you still use it as is. I changed a few things, but I don't get what this is supposed to do. You search for a string and if you find it, you return the same string? In any case, here is the code. I tested it and it works. It will look in column A of sheet Feuil1 right now. Modify to suit your needs.

Sub test()
    MsgBox Find_First2("aa")
End Sub

Public Function Find_First2(FindString As String) As String

    Dim Rng As Range
    Dim TrimString As String

    TrimString = Trim(FindString)

    If TrimString <> "" Then
        With Sheets("Feuil1").Range("A:A") 'This is what you need to modify
            Set Rng = .Find(What:=TrimString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

            If Not Rng Is Nothing Then
                Find_First2 = Rng.Value
                MsgBox ("Found at: " & Rng.Address)
            Else
                Find_First2 = ""
            End If
        End With
    End If

End Function