How to stop a specific VBA loop??

[wenyuanalive]

Hi guys,

I got another question for you.

As you can see in my code, I wrote a sub to show the date from Jan, 1990 to Apr, 2012 in the format of "mmmyy". Each date will lasts for 5 seconds in Cells(A2). However, could anyone tell me how to stop the loop when the date is bigger than Apr. 2012?

Because the most updated materials I have is April,2012, it makes no sense for the dates after that. Thanks guys!


Sub extract()
Dim monthdata(1 To 12) As String
dim yeardata(1 to 23 ) as integer
Dim m, y As Variant
monthdata(1) = "Jan"
monthdata(2) = "Feb"
monthdata(3) = "mar"
monthdata(4) = "apr"
monthdata(5) = "may"
monthdata(6) = "jun"
monthdata(7) = "jul"
monthdata(8) = "aug"
monthdata(9) = "sep"
monthdata(10) = "oct"
monthdata(11) = "nov"
monthdata(12) = "dec"
yeardata(1) = 90
yeardata(2) = 91
yeardata(3) = 92
yeardata(4) = 93
yeardata(5) = 94
yeardata(6) = 95
yeardata(7) = 96
yeardata(8) = 97
yeardata(9) = 98
yeardata(10) = 99
yeardata(11) = 0
yeardata(12) = 1
yeardata(13) = 2
yeardata(14) = 3
yeardata(15) = 4
yeardata(16) = 5
yeardata(17) = 6
yeardata(18) = 7
yeardata(19) = 8
yeardata(20) = 9
yeardata(21) = 10
yeardata(22)= 11
yeardata(23) = 12
For Each y In yeardata
For Each m In monthdata
Application.Goto Reference:="R2C1" ' Select A2
ActiveCell.FormulaR1C1 = m & y
Application.Wait Now + TimeValue("00:00:05") 'Pause 5 seconds to show the date
Next m
Next y
End Sub

[wenyuanalive]

Someone has given me a really sharp suggestion,


Sub extract()
Dim d As Date
d = DateValue("Jan 1, 1990") 'Start date
Do Until d > DateValue("April 1, 2012") 'End Date
Range("A2").Value = Format(d, "'mmm yy") 'Month Year text in cell A2
Application.Wait Now + TimeValue("00:00:05") 'Pause 5 seconds to show the date
d = DateAdd("m", 1, d) 'Add one month to d
Loop
End Sub

[Aussiebear]

As you can see in my code, I wrote a sub to show the date from Jan, 1990 to Apr, 2012 in the format of "mmmyy".

If the above statement is true then how does it get "bigger than April"?

[wenyuanalive]

Hey Aussiebear,

The second post gives the answer. The first question has the date problem, which is what I asked.

[Aussiebear]

Please re-read my post. If the start of the loop is the Jan 1990 and the end of the loop is April 2012 (these are your indicated parameters), I would like to know how does it therefore get bigger than April, if April is the end date of the loop?

[wenyuanalive]

Please re-read my post. If the start of the loop is the Jan 1990 and the end of the loop is April 2012 (these are your indicated parameters), I would like to know how does it therefore get bigger than April, if April is the end date of the loop?

Copy and run my first code. You'll know that it ends Dec 2012

[Paul_Hossler]

It ends in your original because you did For Each m and m=(1, ..., 12)

One way could have been to just exit, but the Do Loop is better




For Each y In yeardata
For Each m In monthdata
if y = 2012 and m = 5 then goto done
Application.Goto Reference:="R2C1" ' Select A2
ActiveCell.FormulaR1C1 = m & y
Application.Wait Now + TimeValue("00:00:05") 'Pause 5 seconds to show the date
Next m
Next y
Done:


Paul

[wenyuanalive]

It ends in your original because you did For Each m and m=(1, ..., 12)

One way could have been to just exit, bot the Do Loop is better




For Each y In yeardata
For Each m In monthdata
if y = 2012 and m = 5 then goto done
Application.Goto Reference:="R2C1" ' Select A2
ActiveCell.FormulaR1C1 = m & y
Application.Wait Now + TimeValue("00:00:05") 'Pause 5 seconds to show the date
Next m
Next y
Done:


Paul


Thanks Paul. That's exactly what I want!

[Aussiebear]

So now we get to actual point of the issue. Whilst you wrote a sub to "try" to show the date from Jan 1990 to April 2012 it actually goes to Dec 2012. If you are looking for assistance then you need to be more accurate in the decsription of what is actually happening in the event.

Please try to be more accurate in the future.

[snb]

Or you could use VBA:


sub snb()
for each x in [row(1990:2012)]
for each y in Application.GetCustomListContents(3)
if format(date,"yyyymmm")= x & y then exit sub
msgbox x & " " & y
next
next
End sub

or


Sub snb_001()
For j = 1 To DateDiff("m", "01-01-1990", Date)
MsgBox Format(DateAdd("m", j - 1, "01-01-1990"), "mmm yyyy")
Next
End Sub

[wenyuanalive]

So now we get to actual point of the issue. Whilst you wrote a sub to "try" to show the date from Jan 1990 to April 2012 it actually goes to Dec 2012. If you are looking for assistance then you need to be more accurate in the decsription of what is actually happening in the event.

Please try to be more accurate in the future.


Please try to solve the problem instead of asking what the problem is in the future.