none
How to stop a specific VBA loop?? RRS feed

  • Question

  • 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
    Sunday, June 3, 2012 4:17 PM

Answers

  • 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

    Sunday, June 3, 2012 5:34 PM

All replies

  • 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

    Sunday, June 3, 2012 5:34 PM
  • Hi Wenyuanalive,

    I’m glad to see that you have solved your issue. Thank you for sharing your experience here. Your solution is very beneficial for other community members who have similar problem.

    Best regards,
    Quist


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    Monday, June 4, 2012 6:20 AM
    Moderator