.FindNext not working in UDF

**magnusga** · 02-20-2013, 09:23 AM

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.

Please Login or Register  to view this content.

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

**cytop** · 02-20-2013, 10:30 AM

Cross posted: http://www.ozgrid.com/forum/showthread.php?t=175339

**magnusga** · 02-20-2013, 11:49 AM

Linked to cross post and changed title.

**shg** · 02-20-2013, 12:09 PM

FindNext doesn't work in UDFs. You need to repeat the Find.

IIRC, Find didn't work in UDFs until Excel 2000, or maybe Excel 97.

**magnusga** · 02-20-2013, 12:57 PM

Thanks a lot shg. Cytop was lending me his brain on the other forum, so continued the discussion there (http://www.ozgrid.com/forum/showthre...324#post650324), as well as linked back here. Fixed the initial issue, but had a few questions re tweaking of the code.

Thanks again!