Multiple Worksheet search for empty cells

Stelli1989 · Feb 28th 2020

Hi,

i've got an excel file which contains more than 50 worksheets. All of them have the same first labeling line.

I am trying to get a vba to work which searches each worksheet for following criteria:

When a Cell in Column A is not empty then check if Cell in same row on Column K and L is empty. If one of them is not empty do nothing.

If both of them are empty check if Column M is empty.

If Column M is empty, check if Date in Column D is max. 3 days in the future Then jump to this row.

Else check next row until all Worksheets are done.

If there are no cells which meets the criteria a MsgBox should appear to confirm that no unfinished rows exist.

I would thank you For any suggestions.

alring · Feb 28th 2020

Hi,

Try this:

Code

Sub Cell_test()

Sheets("Sheet1").Select 'Change "Sheet1" to your sheetname

Row = 1

For iRun = 1 To 15 'Change 15 to the number of row you have

Cells(Row, 1).Select
If Not IsEmpty(ActiveCell) Then
    Cells(Row, 11).Select
        If Not IsEmpty(ActiveCell) Then
            K = 1
        Else
            K = 0
        End If
    Cells(Row, 12).Select
        If Not IsEmpty(ActiveCell) Then
            L = 1
        Else
            L = 0
        End If
    If K = 0 And _
        L = 0 Then
        Cells(Row, 13).Select
            If Not IsEmpty(ActiveCell) Then
            Range("A1").Select
                GoTo Out
            Else
                Cells(Row, 4).Select
                If ActiveCell > Date And _
                    ActiveCell < Date + 3 Then
                    Rows(Row).Select
                    Exit Sub
                End If
            End If
    End If

Else
    GoTo Out
End If

Out:
Row = Row + 1

Next 'iRun

End Sub

Display More

Remember to change Sheet Name And the number of Rows.

Stelli1989 · Apr 1st 2020

Thank you very much for your help.

It works great. I just changed the first Line where you selected the Worksheet to this:

Code

For Each ws In ThisWorkbook.Sheets
ws.Select

So all Worksheets will be searched, one after another.

Thank you

alring · Apr 2nd 2020

Glad to be able to help.

**royUK** · Apr 2nd 2020

Selecting will slow down the code.

Attach an example workbook and I'll take a look.

Multiple Worksheet search for empty cells

Participate now!

Share