Correct FindNext Implementation in Excel User-Defined-Functions
I have a function that searches a column in an Excel sheet for a particular value: UserName.
For each of the cells that contain the UserName, I need to check another cell: LessonDate for the latest value (and return the latest LessonDate value found for the particular UserName).
I wrote a routine with an initial .Find followed by a .FindNext loop in order to search all of the occurences of the UserName. When I tested the Function (I passed it the Sheet to search, and the UserName to search for as parameters), by calling it from the Immediate Window, it worked fine.
However, when I added the function call to a Cell in a sheet, it only found the first occurence of the .Find command, and the .FindNext always came up Null.
I asked for help on the Office Developers Forum and was told that the .FindNext never works for a User Defined Function.
I ended up creating a workaround: successive .Find (After:=<username>), but it would have been much easier had the .FindNext function as documented.
Thank you for your time in advance.