Quote Originally Posted by Marcol View Post
Your workbook is extremely difficult to follow, for me at least

Where are you calling the UDF?

In addition to volatile functions, you have 252 named ranges, references to whole columns, (this in 2007 and above is causing 1,048,576 calculations), etc.

The function MLOOKUP() also calls whole columns

Try this first and see if it returns the result you are expecting
This line in particular is returning 1,048,576 for the Ubound value
For r = fFoundNo To UBound(KA1, 1)

I'm pretty sure that most of the code is redundant, but first we need to clearly establish what you are trying to do.
Many thanks for the response Marcol!

My UDFs are in the sheet "Known Companies" in columns C, E and F.

Sorry for making it too complex. Basically the idea is that each company has its unique number and information corresponding to this number which is imported from external and always updated.

Because one company can have more than one note/tag/occasion, lookup and index/match approaches do not work, thats why I turned to mlookup.


Your code alone allowed for 10 seconds improvement from 84 seconds to 74 seconds

During the time I wrote this response I decided to test using limited number of cells in the row instead of the whole row. It calculated everything in 5 seconds. I think you resolved it - reference to the whole row (A:A; E:E etc) was making the whole think laggy. Thank you so much!!!