excel VBA function works in code but not as UDF in spreadsheet

[wwhoami]

I coded a function in a module that searches column A:A for a string (passed as an input) and returns the full content of the first cell in range A:A that contains that piece of string...

THe function works fine when I execute is from the sub test(), but I cannot make it work in the sheet directly... ie by writing =Find_First("lala"). It just returns "not found" although the piece of text is indeed in A:A

Here's my code.. Thanks for your help!!


<code>Public Function Find_First(FindString As String) As String

Dim Rng As Range
Dim TrimString As String

Application.Volatile True

TrimString = Trim(FindString)

If TrimString <> "" Then
With Sheets("Sheet1").Range("A:A")
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_First = Rng.Value
MsgBox ("Found at: " & Rng.Address)
Else
Find_First = "not found"
MsgBox ("Not found ")
End If
End With
End If

End Function

Sub test()
MsgBox Find_First(" lala ")
End Sub</code>


Another thing to mention is that it seems to work on certain other computers... Maybe it is related to some settings?? I use Excel 14.5.4 (Office 2011 for MAC)...

 

[minimaster]

Using Msgbox in a UDF is not a good idea and I would pass the search area as an argument too:

Formula =Find_First(" lala ",A:A)

Public Function Find_First(ByVal FindString As String, ByRef RngFind As Range)
   Dim rng As Range
   Dim TrimString As String
   Application.Volatile True
   TrimString = Trim(FindString)
   If TrimString <> "" Then
      With RngFind
         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_First = rng.Value
            Set rng = Application.Caller
            With rng
               On Error Resume Next
               .Comment.Delete
               On Error GoTo 0
               .AddComment rng.Address
            End With
         Else
            Find_First = "not found"
            Set rng = Application.Caller
            With rng
               On Error Resume Next
               .Comment.Delete
               On Error GoTo 0
               .AddComment "Not Found"
            End With
         End If
      End With
   End If
End Function

 

[wwhoami]

Thanks minimaster !
Its very strange... I created a brand new excel file, created a module, copied your function as is... and the same thing happens... It works if I call the function with a sub, but not as a UDF...

I tried to code simple functions (ex. a function that adds numbers...) and these ones work... So there's something related to that specific text search function... but what?

Also, my function seems to work on other computers...

I really have no clue why it doesnt work on my machine...!!

 

[shg]

Slightly different:

Public Function FindFirst(sWhat As String, rWhere As Range) As String
  Dim rFind         As Range

  With rWhere
    Set rFind = .Find(What:=sWhat, _
                      After:=.Cells(.Cells.Count), _
                      LookIn:=xlValues, _
                      LookAt:=xlPart, _
                      SearchDirection:=xlNext, _
                      MatchCase:=False)
  End With

  If rFind Is Nothing Then
    FindFirst = "not found"
  Else
    FindFirst = rFind.Address & ": " & rFind.Value
  End If
End Function

For example:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td]

List​

[/td][td]

​

[/td][td]

sWhat​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

2​

[/td][td]Eric Cooper[/td][td][/td][td]Alan[/td][td]$A$4: Alan Smith[/td][td]D2: =FindFirst(C2,A:A )[/td][/tr]
[tr][td]

3​

[/td][td]Eric Cooper[/td][td][/td][td]Barb[/td][td]$A$9: Barb Carpenter[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Alan Smith[/td][td][/td][td]Cain[/td][td]$A$5: Cain Miller[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Cain Miller[/td][td][/td][td]Dana[/td][td]not found[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]Alan Smith[/td][td][/td][td]Eric[/td][td]$A$2: Eric Cooper[/td][td][/td][/tr]
[tr][td]

7​

[/td][td]Alan Smith[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]Eric Cooper[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]Barb Carpenter[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]Barb Carpenter[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]Eric Cooper[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]Eric Cooper[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

The Find method has not always worked in UDFs; support dates back to maybe Excel XP on Windows. I don't know if it works at all on Macs.

 

[wwhoami]

THanks shg!
THe function you sent me doesnt work either unfortunately on my computer...
It actually looks like the find method is not working properly on office excel 2011 for MAC. There's a number of posts about this bug.. But I didnt find any work around! Apart from using a PC !...
Best,

 

[shg]

No surprise -- Macs have always been an afterthought for MS.

If it's important that you have this functionality on a Mac, you can do a linear search.

 

[Rick Rothstein]

It actually looks like the find method is not working properly on office excel 2011 for MAC. There's a number of posts about this bug.. But I didnt find any work around!

See if this alternate code works for you...
[table="width: 500"]
[tr]
[td]

Public Function Find_First(FindString As String) As String
  Dim ColA As String, TextUptoFindString As String, RowNum As Long
  ColA = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), Chr(1))
  RowNum = UBound(Split(Left(ColA, InStr(1, ColA, FindString, vbTextCompare)), Chr(1))) + 1
  If RowNum Then
    Find_First = Cells(RowNum, "A").Value
    MsgBox "Found at: A" & RowNum
  Else
    Find_First = "Not found"
    MsgBox ("Not found")
  End If
 End Function

[/td]
[/tr]
[/table]

 

[shg]

I think you would want to make that volatile if you're not passing a reference.

Also, I think you would want to qualify the range reference so the function output is correct if you switch to another sheet, make a change, then come back.

 

[Rick Rothstein]

I think you would want to make that volatile if you're not passing a reference.

Also, I think you would want to qualify the range reference so the function output is correct if you switch to another sheet, make a change, then come back.

Valid points... thanks for making them. I think this modified code handles the points you raised...
[table="width: 500"]
[tr]
[td]

Public Function Find_First(FindString As Range) As String
  Dim ColA As String, TextUptoFindString As String, RowNum As Long, WS As Worksheet
  Set WS = FindString.Parent
  ColA = Join(Application.Transpose(WS.Range("A1", Cells(Rows.Count, "A").End(xlUp))), Chr(1))
  RowNum = UBound(Split(Left(ColA, InStr(1, ColA, FindString, vbTextCompare)), Chr(1))) + 1
  If RowNum Then
    Find_First = WS.Cells(RowNum, "A").Value
    MsgBox "Found at: A" & RowNum
  Else
    Find_First = "Not found"
    MsgBox ("Not found")
  End If
End Function

[/td]
[/tr]
[/table]

 

[shg]

Will that be responsive to a change in col A?