.FindNext not working in UDF

[magnusga]

Dear Excel Experts,

I have written the code below that works well when I click the play icon in the visual basic screen and run it from there. However, if I type the formula "=Investments()" into a cell and run it from the excel sheet, the find method only finds ONE of the cells it is searching for (ie. Investments() below return only one name). I need to be able to run it from a cell because i am running the function for a lot of rows using ctrl+shift+enter.

Explanation of the function: Each cell in Sheet 1, column A, contains one company name (Apple, Microsoft, etc). Each cell in Sheet 1, column B, contains a string of investors for that company ("3i / Fidelity / Blackstone", "Blackrock / 3i / Bain Capital", etc.). In Sheet 2 I have a column A where each cell contains one private equity investor (Blackstone, Carlyle, etc.), and my function aims to search through column B of Sheet 1 for each of these private equity investors, and concatenate together all the companies they have invested in. So if Blackstone occurred 4 times in Sheet 1, column B, then 4 company names should be listed in Sheet 2, column B, after running the function.

Function Investments() As String
    Dim masterSht As Worksheet, peSht As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSearch As String, foundAt As String, cellValue As String
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim exitLoop As Boolean
    
    On Error GoTo Err
    
    Set masterSht = Sheets("Output")
    Set peSht = Sheets("PE_Overview")
    Set oRange = masterSht.Columns(7)
    
    strSearch = ActiveCell.Offset(0, -5).value
    
    MsgBox "Searching master sheet for: " & strSearch
    
    Set aCell = oRange.Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        MsgBox strSearch & " Found in Cell " & aCell.Address
        Set bCell = aCell
        cellValue = aCell.Offset(0, -5).value
        foundAt = aCell.Address
        
        Do While exitLoop = False
            Set aCell = oRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                cellValue = cellValue & ", " & aCell.Offset(0, -5).value
                foundAt = foundAt & ", " & aCell.Address
            Else
                exitLoop = True
            End If
        Loop
    Else
            MsgBox strSearch & " not Found"
    End If

    MsgBox "The Search String has been found these locations: " & foundAt & ". Company names are: " & cellValue
    Investments = cellValue
    MsgBox Investments
Exit Function
Err:
        MsgBox Err.Description
End Function

Any idea why it runs differently when i click the play button vs when i use it in a cell? Please see attached file for testing purposes.

Thanks a million.

Also posted this question on Ozgrid: http://www.ozgrid.com/forum/showthre...324#post650324


Best regards,

Magnus