Apologies but I realised I posted this in general excel section and not the VB/macro section so this is a duplicate...but please help me
Hi all, i'm a self taught fan of excel & vb but i'm still very much a novice when it comes to using some of the more advanced functions and my main problem at the moment seems to be that my code always ends up being very long winded and could very likely be simplified by quite some degree. This is probably as I still don't quite know what i'm doing :-S. I find that i'm often compelled to using select and activate functions which i'm aware is bad practice and can considerably slows things down.
I've created a macro that basically searches a relatively large number of worksheets (approx 50) for a user defined unique number and then replaces another value (the price) in the same row as the unique number. This can be repeated up to 8 times depending on how many unique numbers are being searched for! HOWEVER...this is taking a HUGE amount of time to complete. Between 30 seconds for 1 unique number search and 200 seconds for all 8. Although this means not having to scroll through sheets and sheets to locate these individually it still takes about the same amount of time which defeats the object.
I'd like to see firstly if somebody could please please please help me simplify the code which might speed things up and secondly if anyone could explain to me why when I hit the Escape key while the macro is running it completes the function in super lightning fast time (we're talking about a second) instead of the usual 30-200 second delay?! I just don't understand :-(
My code:
Bookmarks