General help simplifying my super slow VB code PLEASE :-D

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:

Please Login or Register  to view this content.

Originally Posted by blackcat_78uk

Sorry WhizBang is that what you mean?

Yes. However, in the future please edit your original post instead of re-posting all your code. I have cleaned things up for you because you are new.

Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I am encountering an error on the forum that will not allow me to edit the original post. Please edit your original post to add code tags.

Here's a few suggestions. Always define a sheet and a range and then you don't have to select them.

Here's some code to put at the top, you can probably figure out where.

Please Login or Register  to view this content.

Then down where you do the active cell offset stuff put this.

Please Login or Register  to view this content.

Another example would be writing this code:

Please Login or Register  to view this content.

Like this:

Please Login or Register  to view this content.

EDIT: I changed the first code I posted, I left out a couple of lines.