Excel VBA that uses nested If statements for multiple worksheets

Question

I'm trying to get an output in column U for multiple worksheets in a workbook. The return string will either be "Yes" or "No" depending on which column is not blank and if the difference between two dates are > 150. This is the code I have written, but nothing shows up in column U. Could anyone help me figure out why this isn't working?

Sub Compliance()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
Dim i As Integer
Dim listLength
listLength = ws.Cells(Rows.Count, "M").End(xlUp).Row - 1


For i = 2 To listLength + 2
If IsEmpty(ws.Range("P" & i)) = True And IsEmpty(ws.Range("O" & i)) = True And IsEmpty(ws.Range("N" & i)) = True And DateDiff("d", ws.Range("M" & i), ws.Range("K" & i)) > 150 Then
    ws.Range("U" & i) = "Yes"
ElseIf IsEmpty(ws.Range("P" & i)) = True And IsEmpty(ws.Range("O" & i)) = True And DateDiff("d", ws.Range("N" & i), ws.Range("M" & i)) < 150 Then
    ws.Range("U" & i) = "Yes"
ElseIf IsEmpty(ws.Range("P" & i)) = True And DateDiff("d", ws.Range("O" & i), ws.Range("N" & i)) < 150 Then
    ws.Range("U" & i) = "Yes"
ElseIf DateDiff("d", ws.Range("N" & i), ws.Range("M" & i)) < 150 Then
    ws.Range("U" & i) = "Yes"
Else
    ws.Range("U" & i) = "No"
End If
Next
Next ws


End Sub

Even if all your tests fail you should at least see "No". Do you get any errors when you run it? — Tim Williams, May 17 '17 at 23:39
FWIW (almost certainly not related to the problem) `ws.Cells(Rows.Count, "M")` should be `ws.Cells(ws.Rows.Count, "M")` — YowE3K, May 18 '17 at 00:24
@A.S.H even if he step into it with the debugger he won't get anything wrong because codes are actually ok (instead of defining a variable as YowE3K has mentioned) the problem is occurring because of looking at wrong worksheet as his loop starts from Sheet1 probably he is at somewhere else. — Mertinc, May 18 '17 at 02:18

score 0 · Accepted Answer · edited Sep 21 '17 at 15:40

When you start your loop with For Each ws In ThisWorkbook.Worksheets then Excel starts processing from Sheet1 and I'm sure your list in another sheet.

So, the reason nothing changes in your U column is, because you are looking at wrong Worksheet.

As long as you would like to loop through all of your worksheets, start to follow your lists from Sheet1. If you would like to loop in different order, you should define this in your code.

And below I edited some part of your code and also added msgbox ws.name to show you in which sheet Excel is working now.

Why Use Integer Instead of Long?

Option Explicit

Sub Compliance()

Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    Dim i As Long
    Dim listLength
    MsgBox ws.Name
    listLength = ws.Cells(ws.Rows.Count, "M").End(xlUp).Row - 1

        For i = 2 To listLength + 2
        If IsEmpty(ws.Range("P" & i)) = True And IsEmpty(ws.Range("O" & i)) = True And IsEmpty(ws.Range("N" & i)) = True And DateDiff("d", ws.Range("M" & i), ws.Range("K" & i)) > 150 Then
            ws.Range("U" & i) = "Yes"
        ElseIf IsEmpty(ws.Range("P" & i)) = True And IsEmpty(ws.Range("O" & i)) = True And DateDiff("d", ws.Range("N" & i), ws.Range("M" & i)) < 150 Then
            ws.Range("U" & i) = "Yes"
        ElseIf IsEmpty(ws.Range("P" & i)) = True And DateDiff("d", ws.Range("O" & i), ws.Range("N" & i)) < 150 Then
            ws.Range("U" & i) = "Yes"
        ElseIf DateDiff("d", ws.Range("N" & i), ws.Range("M" & i)) < 150 Then
            ws.Range("U" & i) = "Yes"
        Else
            ws.Range("U" & i) = "No"
        End If
        Next

    Next ws

End Sub

Thank you! I have changed ws.Cells(ws.Rows.Count, "M") in my code and I have also added an <> "TOTALS" to the If statements so the code does not run on the first sheet. Everything works now! — T. Fukunaga, May 19 '17 at 00:48
Glad to hear that. Have enjoy it. And as long as the question is answered you can approve this, as an *answer* for future investigations of others about this subject. — Mertinc, May 19 '17 at 01:08

Excel VBA that uses nested If statements for multiple worksheets

1 Answers1