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
Bookmarks