Problem with DO ... LOOP UNTIL ...

Question

Hello!

I've got another problem.

With this code I would like to notice empty cells from A1 to D4. But for unknown reasons I programmed an endless loop.

Sub test()

Dim Rows,Columns,MaxRows,MaxColumns As Integer

Dim Address As String

Rows=0

Columns=1

MaxRows=4

MaxColumns=4

DO

DO

DO

rows=rows+1

LOOP UNTIL Cells(rows,columns)=""

Address=Address & Cells(rows,columns)

LOOP UNTIL rows = MaxRows

Columns=Columns+1

Rows=0

LOOP UNTIL Columns=MaxColumns

MsgBox "Empty Cells: " & Address

Either I get a run time error or I program an endless loop. Very wired.

Example for endless loop (Z means Row, S is for Column)

Sub LoopNo2()

Dim Z, S As Integer

Z = 0

S = 1

'If Cells(Z, S) = "" Then MsgBox "Leer"

Do

Do

Z = Z + 1

Loop Until Cells(Z, S) = ""

MsgBox "Z: " & Z

Loop Until Z = 4

End Sub

Answer 1

Hello!

I noticed one thing.

If there's a #NV in a cell then the program will crash.

Example:

When in A1 the formula =SQRT(a) it will return #NV.

How can I include it?

Answer 2

Hi Dominique,

Thank you for returning and posting the code for the solution to your problem. You are invited return and chip in by answering questions you may know the answer to. Good answers like yours are always welcome.

Answer 3

Hello,

I found a solution using three DO LOOP UNTILs.

Zeile means Row, Spalte means Column

It counts every empty cell from A1 to H30.

Dim zaehler, spalte, zeile2, spalte2 As Long

Dim adresse As String

adresse = ""

zaehler = 0

spalte = 1

zeile2 = 30

spalte2 = 8

Do

Do

Do

zaehler = zaehler + 1

Loop Until Cells(zaehler, spalte) = ""

If Cells(zaehler, spalte) = "" Then adresse = adresse & " " & Cells(zaehler, spalte).Address(0, 0)

Loop Until zaehler = zeile2

spalte = spalte + 1

zaehler = 0

Loop Until spalte = spalte2 + 1

MsgBox adresse

Answer 4

I'm not positive I fully understand what you intend, but in addition to Bob's comments, I'd suggest either (a) using For...Next loops instead:

Public Sub ReportBlanks()

Dim nMaxRows As Long

Dim nRow As Long

Dim nMaxColumns As Long

Dim nColumn As Long

Dim sAddress As String

nMaxRows = 4

nMaxColumns = 4

For nRow = 1 To nMaxRows

For nColumn = 1 To nMaxColumns

If IsEmpty(Cells(nRow, nColumn)) Then _

sAddress = sAddress & ", " & Cells(nRow, nColumn).Address(False, False)

Next nColumn

Next nRow

If sAddress = vbNullString Then

MsgBox "No blanks"

Else

MsgBox "Blanks: " &  Mid(sAddress, 3)

End If

End Sub

or (b) using SpecialCells:

Public Sub ReportBlanks2()

Dim nMaxRows As Long

Dim nMaxColumns As Long

Dim sAddress As String

nMaxRows = 4

nMaxColumns = 4

With Cells(1, 1).Resize(nMaxRows, nMaxColumns)

On Error Resume Next    'in case there are no blanks

sAddress = .SpecialCells(xlCellTypeBlanks).Address(False, False)

On Error GoTo 0

End With

MsgBox "Blanks: " & sAddress

End Sub

Answer 5

OK, here are a few things.

1. Not that it matters for this example, but your DIM statement is probably not doing what you want or expect. Only MAxColumns is dimmed as an integer. The other variables are dimmed as variants. What you probably meant to say is: Dim Rows as integer, columns as integer, maxrows as integer, maxcolumns as integer
2. For this small example, it does not matter, but any reference to rows should be dimmed as a long, not as an integer. An Integer's maximum value is 65,535, and will only work properly for xls file, not xlsx files it you expect the maximum number of rows to be near the sheet limit.
3. You may have an infinite loop (well, not exactly infinite) if there is a blank in row three. Assume A3 is blank, and A4 is not. When you find the blank cell in A3, you update the address, and then go back to the inner loop which updates rows to 4. If A4 is not blank, you return to the inner loop and update rows again. At this point, rows is 5 and will never be equal to maxrows.

4)Your loops should probably be governed by loop until rows>maxrows. Likewise with columns.

5)If you are trying to get a list of addresses of blank cells, this will not do it. The statement address=address & cells(rows,columns) appends the CONTENTS of cells(rows, columns) to address. You have already shown that the contents of this cell is blank. What you probably mean to do is: address=address &"," &cells(rows.columns).address

I hope this helps.