How do I reset a for next loop in the script?

**BrianATrease** · 10-15-2012, 11:59 AM

Hello friends,

I have a nested for-next loop inside another for-next loop, but when the nested loop ends it does not reset to the beginning again for the next item in my array loop. I am pretty sure the problem lies in my declarartions and how VBA handles arrays used in For-Next loops. I am having a mental block concerning declaring arrays and their ranges. An example of the setup is below;

Please Login or Register  to view this content.

How would I properly use these two for-next loops such that "x" is reset to 1 each time a new member of the array is called?

Thanks for any help, Newbee BAT:-)

**patel45** · 10-15-2012, 12:05 PM

Please Login or Register  to view this content.

**BrianATrease** · 10-15-2012, 12:16 PM

I didn't explain due to format control of post window, but the lines are staggered based upon hierarchy of script in the sheets I am working with. The staggering did not help there either. "x" still goes to 100 and does not reset. Thanks though, BAT:-)

**mshale** · 10-15-2012, 12:17 PM

I'm sure you have a reason for looping through 100, but if you don't, you can simplify your code and resolve the issue by just avoiding it...

Please Login or Register  to view this content.

But, if you MUST loop to 100, then try this:

Please Login or Register  to view this content.

Originally Posted by BrianATrease

I didn't explain due to format control of post window, but the lines are staggered based upon hierarchy of script in the sheets I am working with. The staggering did not help there either. "x" still goes to 100 and does not reset. Thanks though, BAT:-)

If you had used code tags, your formatting would have remained

**stanleydgromjr** · 10-15-2012, 12:20 PM

BrianATrease,

Welcome to the Excel Forum.

You do not have to use Select, or loop thru rows 1 to 100, in your macro.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Test macro.

**BrianATrease** · 10-15-2012, 03:42 PM

I am not really pasting the number 12 to each cell, in all actuallity I am pasting an entire spreadsheet of data from two other sources with calculations and references. Too much to add to this post. just trying to make it simple. As to the second fix, I tried that some time ago and the for next loop still does not reset for some reason (this is confusing since even the cobol and fortran I learned with allowed for reset on re-declaration of the for statement). Thanks though, BAT

**BrianATrease** · 10-15-2012, 04:16 PM

help on for-next.xlsmIf it helps I have posted the entire spreadsheet with the vba. I am only concerned with the reasoning behind the failed reset of the multiple for-next loops (yes there are several).

In the Macros list is the file I wanted help with (Macro1) and the eventual fix I used for the problem (Button_3_click). I am not looking for a fix to the problem in my macros, but only an understanding of how to use multiple for-next loops such that they reset.

I see these as a clock-work where each 60 seconds the next array element (minutes) updates and the for-next "seconds" loop repeats. The only problem I have is how to tell the clock to reset the seconds hand for the next minute.

I know this is a simple problem and is covered in VBA 101, first week or so, but I am at a loss.

Please help me understand for future reference. Thanks, Brian Trease

**mshale** · 10-16-2012, 09:37 AM

I'm not sure if you actually looked at my code in post #4, but it is not the same as the previous suggestions (if that is what you were referring to by "tried that some time ago"). I will post a sample from your code that has the same methods as mine in post #4:

Please Login or Register  to view this content.

Notice the position of x = 2. This will reset x to 2 with each iteration of samploc1.

**BrianATrease** · 10-18-2012, 12:22 PM

All apologies, and yes it does work quite well now.

I did indeed try the reset value before the for statement some time ago, but I believe the "Next" statements were in the wrong order and caused the failure of the count. I kept thinking there was some unwritten rule for resetting the count. Its just me trying to make things difficult;-).

Thanks and KUDOS to you for all your help! Hail to mshale!

BAT :-)