stopping a do until loop in Excel-vba

Question

I created this macro to correct an error (to fill blank created by Feb 29 in non-leap years)i have been facing problem in stopping the Do Until loop.

the macro does what it is supposed to do but it is not working as supposed to with Do Until ActiveSheet.Cells(3, x) = "2012" i want it to stop when cell (3,x) is 2012

Sub Feb_CORRECTION()
Dim i, x As Integer
Dim year
Dim leapyear

Range("c64").Select
x = 3
Do Until ActiveSheet.Cells(3, x) = "2012"
year = ActiveSheet.Cells(3, x)
leapyear = year Mod 4
If leapyear > 0 Then
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(-1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 1).Select
Else
ActiveCell.Offset(0, 1).Select
x = x + 1
End If
Loop

End Sub

thank you

Make sure you are comparing the same data types. Since "2012" is a string, try comparing it with `CStr(ActiveSheet.Cells(3, x).Value)` instead. — Olle Sjögren, Sep 09 '12 at 12:23
Also, try to step through the code to make sure that the conditions are what you think they are. — Olle Sjögren, Sep 09 '12 at 12:26

Jon Crowell · Accepted Answer · 2012-09-09T17:21:33.673

Your loop is set to exit if the value in row 3 in column x is "2012".

You initialize x to 3, then check to see if the year in row 3 is a leap year. You only increment x if it is, so unless the year in column "C" is a leap year, x will never get incremented.

Try this instead:

Sub Feb_CORRECTION()
Dim i As Integer
Dim x As Integer

Dim year As Integer
Dim leapyear As Integer

    Range("c64").Select
    x = 3

    Do Until ActiveSheet.Cells(3, x) = "2012"
        year = ActiveSheet.Cells(3, x)
        leapyear = year Mod 4

        If leapyear > 0 Then
            Range(ActiveCell, ActiveCell.End(xlDown)).Select
            Selection.Cut
            ActiveCell.Offset(-1, 0).Select
            ActiveSheet.Paste
            ActiveCell.Offset(1, 1).Select
        Else
            ActiveCell.Offset(0, 1).Select
        End If

        ' increment x regardless of leap year status
        x = x + 1
    Loop

End Sub

You also have several variables declared as variants, which is a bad idea unless you have a very specific reason for using them. Variants can lead to bugs that are very hard to track down. I've fixed that in the snippet above.

Note that this line in your code is declaring i as a variant and x as an integer:

Dim i, x As Integer

You've also declared both year and leapyear as variants. They should probably be integers instead.

Final comment: format your code with indents. It is much easier to understand what it is doing if formatted correctly.

stopping a do until loop in Excel-vba

1 Answers1