CODEWITHSUNDEEP
excelvbaloopsdo-while
Elizabeth
Elizabeth

Reputation: 241

VBA Do-while function does not check the condition (with error pictures)

The dataset is like this enter image description here

I want to delete the same value in column C and take the average of the corresponding values in column D. But it seems the Do-Until function does not check the condition?

the first check about the line number of the first duplicated No., which works fine enter image description here the second check shows the pointer mover to line 6 where the value in the C column is empty, and the Do-while function should not run, but the message box still popped up and followed an error warning enter image description here enter image description here

i = 2

'Do while Sheets(1).Range("C" & i).Value is not empty
Do While IsEmpty(Sheets(1).Cells(i, "C").Value) = False

    If Sheets(1).Cells(i, "C").Value = Sheets(1).Cells(i + 1, "C").Value Then
        StartNo = i
        MsgBox StartNo
        Do While Sheets(1).Cells(i + 1, "C").Value = Sheets(1).Cells(i, "C").Value
            i = i + 1
        Loop
        EndNo = i
                          
        Sheets(1).Range("D" & StartNo) = WorksheetFunction.Average(Range("D" & StartNo & ":D" & EndNo))
        Sheets(1).Rows(StartNo + 1 & ":" & EndNo).Delete
        i = StartNo
    End If
    i = i + 1
    Loop

Upvotes: 1

Views: 371

Answers (1)

Tim Williams
Tim Williams

Reputation: 166156

You could get "overflow" if you have declared i as Integer and your code loops to a row number above 32,767 (the max size of an Integer in VBA).

It could do this if a value in Column C only appeared to be blank, but was (for example) an empty string value. You can create a such value by entering a formula ="" and then performing a copy/paste values on the cell(s): it looks empty, but IsEmpty(cellReference) returns False, as does the worksheet formula ISBLANK().

So IsEmpty()=False passes your outer Do While test, and since the value of any empty cell below the current "not empty" one will be equal to the current cell (because the value of an empty cell is equal to one with an empty string) now your inner Do While just continues on down the column until you get the overflow as the value of i gets too large.

Working directly with ranges can be a little easier to follow:

Sub Tester()
    
    Dim c As Range, n As Long, v
    
    Set c = Sheets(1).Cells(2, "C") 'start cell
    Do While Len(c.value) > 0       'loop while have data (length check is safer than `IsEmpty()`)
        n = 1                       'reset replicates counter
        v = c.value                 'read once...
        Do While c.Offset(n).value = v
            n = n + 1               'increment replicate count
        Loop
        If n > 1 Then               'have replicates: populate average and delete unwanted rows
            c.Offset(0, 1).value = Application.Average(c.Offset(0, 1).Resize(n))
            c.Offset(1).Resize(n - 1).EntireRow.Delete
        End If
        Set c = c.Offset(1, 0)      'next row
    Loop
    
End Sub

Upvotes: 1

Related Questions