Custom Lookup UDF Issue

I tried to recreate as per your steps but the formula still worked fine (not sure if thats because I'm using XL 2003).

Have you tried protecting cell D5 and B5 when runnning the macro? - macro might be trying to change something that you aren't noticing and will throw an error if its protected...

I'll see if I can find a computer around here that still has 2003 but our office is all 2007 now... Thanks for taking time to look at it.

you might also want to protect all your 'data' sheets and run the macro?

although from the sounds of it (especially as you say it works fine if the file is saved before running the macro) it might be a bug?

It certainly seems to acts like a bug to me, but I thought I would see if anyone has some masterful debugging skills that could track down the root cause.

in your UDF you have the variable vVal1

is this variable perhaps used in one of your other macros and is confusing the UDF?

There is only the one UDF in that test file and no Macros to start. The only macros are the ones that get recorded which would have no variables. I found a computer with Excel 2003 and tested the file and it crapped out the same as in 2007. On the second run through of creating a macro that deletes a random cell the lookup in D5 goes to #Value. (Scratch Head)

Found a work around for this issue. It would seem that when the .Find is used within a UDF that is going to be used from a Sheet Cell that random recalculates from Excel will cause it to fail because the ranges the .Find is working on are not ready.

I was however able to use the Match function to get the same lookup affect and it is stable during these recalculations. If anyone wants to see a sample file let me know and I will do one up and post.

Thanks.