for loop anomaly

**proudgeek42** · 02-24-2021, 12:34 PM

Hello, I have a code where, when I click the button, the "for" loop goes through some of the data and suddenly jumps out of the loop and gives me the dialog box saying that it's done. In an effort to find out why, I stepped through using F8. Doing that allowed it to finish the "for" loop for all of the data. When holding down F8, it jumped out again. The code is below. I could not find anything like this online. Do I need to slow the code down somehow? Doesn't that defeat the purpose of a macro? Please let me know if you need the full code and a sample spreadsheet. It will take me time to put that together, but hopefully the code below shows some obvious error that I missed.

Please Login or Register  to view this content.

**MarvinP** · 02-24-2021, 01:08 PM

Hi proudgeek,

Looks like you need to learn about "Select Case" in your code. Attach the workbook and we can shorten your code.
https://docs.microsoft.com/en-us/off...case-statement
Read the Yellow banner to learn how.
Also "DoEvents" may be needed to allow other OS things to happen and not to jump out of your code.

**proudgeek42** · 02-24-2021, 02:10 PM

Attached is a sample spreadsheet. I have a lot more data (labeled as A-Z in this sheet to limit the amount of data).
I ran the macro on this sheet (button click) and it worked perfectly. I assume the problem comes with a lot more data.
I appreciate help to shorten my code too. I'm not very proficient or efficient at VBA yet.
In the interest of learning, if possible and not too much trouble, please explain very basic theory behind modified code.
I know the Microsoft link does that, but I often don't understand what they are saying as they don't put it in laymen's terms that I can understand.

**proudgeek42** · 03-19-2021, 09:03 AM

I think I may have fixed it on my own. What I did was to move the lastCol variable to after calling another function. Also, I took the formatting out of the loop and highlighted the entire set of data to format the lines between them. I will mark this as solved for now. I did some research on select case and it might clean the code up a little bit, but based on other people's studies it doesn't seem to speed up the code very much more than if statements. Another thing that I may have benefited from would have been to use an array to copy/paste the data (which wasn't the problem, but I want people to consider that). It saves the screen flashing and performs much faster. Eh, next time.