A VBA code built for exchanging images position and numbers is working fine however it's extremely slow to accomplish the process any advises what might be wrong?
Thank you in advanced![]()
Please Login or Register to view this content.
A VBA code built for exchanging images position and numbers is working fine however it's extremely slow to accomplish the process any advises what might be wrong?
Thank you in advanced![]()
Please Login or Register to view this content.
Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.
Crossposted: http://www.ozgrid.com/forum/showthread.php?t=205166
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.
Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!
Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
There are a couple of things going on here
The first few lines of code have been commented out; these lines help speed up code execution so un-commenting them will help; although that said only ScreenUpdating & EnableEvents will really have much of an effect with the operations you're doing, so you can probably lose the other ones. You'll also need to un-comment out the respective matching lines at the end of the sub-routine to re-enable these settings.
Also the structure of the code is such that for each cell you test you're looping all pictures in the sheet. Granted you exit the loop when you've found a match; but worst case scenario you're redundantly looping through all the pictures multiple time.
A better approach would be to just loop the pictures once and test each picture name against the cell range values using a switch statement, like this...
Note: the above code is untested.![]()
Please Login or Register to view this content.
I also put this code in a Worksheet_Change event, your original code ran every time the sheet re-calculated, which was likely causing a lot of unnecessary code executions and wasted overhead. I have written the Worksheet_Change code so that it exits unless one of the cells you're comparing against has changed.
I also not really sure what your timer function was doing, other than seeing how long it takes your machine to count a million! I have added timing to this sub-routing as well so you can see how long it takes to execute.
Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.
If you are unclear about the request or instruction then send a private message to them asking for clarification.
All participants:
Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.
There should be no further responses until cross-post links have been added by the OP.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
My apology i just posted the question to receive asap help, I apologize again. I can't edit the question to add the link as needed, is it considered ok?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks