#REF error happened at RANDOM when running a macro?

One of my coworkers was using a macro I created and found an error that neither I, or he, could replicate after the fact.

The idea is that I have a range of numbers in one worksheet that I want to multiply by a fraction and paste them in another worksheet. There are a couple ways of doing this (most notably the simple copy/paste special method) but for whatever reason when I wrote this last year I did it by using the Evaluate function.

Here is a simplified version of what was happening. Instead of displaying the new values on the 2nd worksheet, it showed the #REF problem. I also want to note that when he ran it, it worked perfectly for 18/19 iterations of a loop that I have it in, and like I said before, we couldn't replicate it for any iterations when I tried again today. This leads me to believe that maybe he was mutli-tasking and pressing something else on the keyboard that may have affected it. This was the case for a different macro we have - when a user was pressing "Shift" to type an email, which caused the sub to stop because it was trying to open a workbook with an Open Event but holding down shift prevents that from working.

Please Login or Register  to view this content.

Great, I might suggest adding not just a sheet reference in the code but also a workbook reference, if this is a common scenario.

sure you can usually do stuff while macro runs... could click on the taskbar for example and activate even a totally different instance of excel.. alt-tab would also switch... lots of things like that can happen