0

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
wwhoami
  • 11
  • 1
  • 3
  • I tried the function and it worked, a strange question maybe but are you sure that you have macros enabled? Otherwise could you maybe provide more info on what your text string could be and what values you are looking at to compare and find? – DragonSamu Aug 27 '15 at 11:58

1 Answers1

0

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
David G
  • 2,315
  • 1
  • 24
  • 39
  • Add a stopping point in the code at the start of the function, and when you call it, press F8 to debug line by line. Tell me which part of the If it enters. OR add msgbox "Nothing found!" before Find_First = "". If you get the message, it means it didn,t find it, not thta it doesn't work. – David G Aug 27 '15 at 15:52
  • Oh I think you are writing the code in a SHEET not a MODULE. Could that be it? See this: http://www.cpearson.com/excel/writingfunctionsinvba.aspx When I write this exact code I posted in a Module, Excel recognises the function when I'm typing it in a cell (same like other functions). I write `=Find_First2("aa")` in any given cell and the msgbox pops up saying `Found at: $A$8` because that's where I wrote `aa`. Then it is written `aa` in the cell I wrote the function in. – David G Aug 27 '15 at 15:55
  • Thank you ! The sub works perfectly ! However, I when I try to use the function in my spreadsheet, it doesnt return anything. I'm probably msising something fundamental... I have a list of strings in a column and in the next column I would like to activate Find_Firt2 for each of these strings (ie return the full content of the cell of my database that contains the first occurrence of each string).. Typically, I have "aa" in the first column (say, in cell A5) and I write "=Find_First2(A5)" in cell B5 ... it returns a blank – wwhoami Aug 27 '15 at 15:59
  • We're overlapping our comments, so when you confirm that you placed it in a module and did what I wrote in the previous one I'll try something else if it still doesn't work. – David G Aug 27 '15 at 16:02
  • My code is in a Module... not a Workbook... Also, when I add a simple function like "rectangleArea" (I found it in the link you sent me), then this function RectangleArea works fine ! I will try to use the debugger now... – wwhoami Aug 27 '15 at 16:07
  • Could it be that the column you're searching in really does not have the value? If you replace the 'not found' part of the If with Find_First2 = "Not Found", does it write Not Found in the cell? Also read the "UDFs And Calcuations" part of the link i provided, it has some importance for this function here. – David G Aug 27 '15 at 16:11
  • I added MsgBox ("Not found ") to the "else" branch. This is what pops up. So It seems that the function actually doesnt find the string when I activate it directly from the spreadsheet ... However the function works when I activate it from you Sub Test... Could this have to do with the way I pass the input in the function? – wwhoami Aug 27 '15 at 16:15
  • I'm not sure why it doesn't pick it up. Maybe it doesn't recalculate it well. If you make the application volatile like in the link I sent "UDFs And Calcuations" part does it still not work? If not you might want to start a new question "Function works in code but not as a UDF in Sheet" – David G Aug 27 '15 at 16:25
  • THanks David G! I made the function volatile and the problem persists... I'll start a new question as you suggest ! – wwhoami Aug 27 '15 at 16:38