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

Question

The dataset is like this

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 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

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

IsEmpty "cheats" sometimes when the cell contains e.g. a space char. You may consider using Val if you are sure Column C will contain positive number, like this While Val(Sheets(1).Cells(i, "C").Value)) > 0. — AcsErno
– AcsErno, Commented Jan 23, 2022 at 19:26

Tim Williams · Accepted Answer · 2022-01-25 07:27:25Z

1

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

edited Jan 25, 2022 at 7:27

answered Jan 23, 2022 at 19:38

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Elizabeth Over a year ago

thank you so much, your code is perfect. I have changed accordingly in my code. But still, I don't know why my original code is not working, sometimes I as wonder if VBA has some intrinsic error, but it shouldn't be, right? given it has run so many decades...

Tim Williams Over a year ago

Added some attempt to explain your original error - basically as @AcsErno pointed out, using IsEmpty() to check for content can sometimes be unreliable.

Elizabeth Over a year ago

thank you so so much for the extra explanation!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related