Results 1 to 5 of 5

.FindNext not working in UDF

Threaded View

  1. #1
    Registered User
    Join Date
    02-20-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question .FindNext not working in UDF

    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
    Attached Files Attached Files
    Last edited by magnusga; 02-20-2013 at 11:48 AM. Reason: Attached file and changed title

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1