IF logic for all date variables empty

Question

I'm trying to modify the below function to include logic where if the variables PPD_1_Date, PPD_2_Date and TSpot_Date are all empty (blank) then output to my "Error" worksheet.

I have rows that should fall under this logic, however they are falling under the Else condition instead.

Function PPDdate()

Dim PPD_1_Date As Date
Dim PPD_2_Date As Date
Dim TSpot_Date As Variant
Dim i As Long, j As Long, k As Long

j = Worksheets("PPDCI").Range("A" & Rows.Count).End(xlUp).Row + 1
k = Worksheets("Error").Range("A" & Rows.Count).End(xlUp).Row + 1
For i = 2 To lstrow

PPD_1_Date = Worksheets("Data").Range("AW" & i)
PPD_2_Date = Worksheets("Data").Range("BA" & i)
Entity = Worksheets("Data").Range("J" & i)
Dept = Worksheets("Data").Range("M" & i)
TSpot_Date = Worksheets("Data").Range("AS" & i)

If PPD_1_Date > PPD_2_Date Then
   Worksheets("PPDCI").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
   Worksheets("PPDCI").Range("F" & j).Value = PPD_1_Date
   Worksheets("PPDCI").Range("G" & j).Value = Worksheets("Data").Range("AX" & i).Value
   Worksheets("PPDCI").Range("H" & j).Value = Worksheets("Data").Range("AZ" & i).Value
   Worksheets("PPDCI").Range("I" & j).Value = Worksheets("Data").Range("AY" & i).Value
   j = j + 1
Else
  If PPD_1_Date < PPD_2_Date Then
    Worksheets("PPDCI").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
    Worksheets("PPDCI").Range("F" & j).Value = PPD_2_Date
    'Worksheets("PPDCI").Range("G" & j).Value = "ELSE IF CONDITION"
    Worksheets("PPDCI").Range("G" & j).Value = Worksheets("Data").Range("BB" & i).Value
    Worksheets("PPDCI").Range("H" & j).Value = Worksheets("Data").Range("BD" & i).Value
    Worksheets("PPDCI").Range("I" & j).Value = Worksheets("Data").Range("BC" & i).Value
    j = j + 1
  Else
    'If IsEmpty(Worksheets("Data").Range(PPD_1_Date & i).Value) = True And IsEmpty(Worksheets("Data").Range(PPD_2_Date & i).Value) = True Then
      'GoTo EmptyRange
    'Else
    If (InStr(1, Entity, "CNG Hospital") Or InStr(1, Entity, "Home Health") Or InStr(1, Entity, "Hospice") Or InStr(1, Dept, "Volunteers") Or ((IsEmpty(PPD_1_Date) = True) And (IsEmpty(PPD_2_Date) = True))) And IsEmpty(TSpot_Date) = True Then
    Worksheets("Error").Range("A" & k & ":H" & k).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
    Worksheets("Error").Range("F" & k).Value = "REVIEW PPD DATA"
    k = k + 1
    Else
    Worksheets("PPDCI").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
    Worksheets("PPDCI").Range("F" & j).Value = TSpot_Date
    Worksheets("PPDCI").Range("G" & j).Value = Worksheets("Data").Range("AX" & i).Value
    Worksheets("PPDCI").Range("H" & j).Value = Worksheets("Data").Range("AY" & i).Value
    Worksheets("PPDCI").Range("I" & j).Value = "NO PPD DATES BUT HAS TSPOT DATE1"
    j = j + 1
    
    End If

  End If
  
End If
'EmptyRange:

'k = k + 1

Next i

End Function

Here is the code I added to the other OR logic; Or ((IsEmpty(PPD_1_Date) = True) And (IsEmpty(PPD_2_Date) = True))

Example row has empty cells in columns AW, BA, and AS, so it should write to my Error worksheet. Is there a syntax or logic issue? I did initially have TSPOT_Date defined as a Date variable, however I was getting a '1004' runtime error (I think because some column rows are empty) so I changed to Variant, however logic still doesn't work as I expect.

I would say that it's your parenthesis being misplaced in that line. You can clean it up by changing `(IsEmpty(PPD_2_Date) = True))` to the equivalent `IsEmpty(PPD_2_Date)` — Marcucciboy2, Aug 20 '18 at 16:28
However, note that when you say `If InStr(...)` you need to say `If InStr(...) > 0` — Marcucciboy2, Aug 20 '18 at 16:29
@Marcucciboy2 I've removed the paranthesis and added the > 0 to the If statement, however it still does not work. I even added a completely seperate If statement before the others in the function and it's still is not working. — Nick, Aug 20 '18 at 16:37
This is my seperate code I added that doesn't work either: `If IsEmpty(PPD_1_Date) = True And IsEmpty(PPD_2_Date) = True And IsEmpty(TSpot_Date) = True Then Worksheets("Error").Range("A" & k & ":H" & k).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value Worksheets("Error").Range("F" & k).Value = "REVIEW PPD DATA888" End If` — Nick, Aug 20 '18 at 16:38
@Marcucciboy2 I added a seperate If statement above (to rule out issue with main IF) and I still do not get expected results. — Nick, Aug 20 '18 at 16:45
Turns out the problem is that you can't check if `Date` variable are 'empty' using `isEmpty()` or even `Len()` because the default value for a date is `30-Dec-1899 00:00:00`, so there is **always** a value in a `Date` variable. Instead, you should check like this `If PPD_2_Date = 0 then` https://stackoverflow.com/a/33421769/2727437 — Marcucciboy2, Aug 20 '18 at 16:51
@Marcucciboy2 I tried your suggestion and I now get 1 row that writes to the worksheet, however I should have more than 1. Kind of strange... — Nick, Aug 20 '18 at 17:02
This is the code I added: `If PPD_1_Date = 0 And PPD_2_Date = 0 And TSpot_Date = 0 Then Worksheets("Error").Range("A" & k & ":H" & k).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value Worksheets("Error").Range("F" & k).Value = "REVIEW PPD DATA888" End If` — Nick, Aug 20 '18 at 17:03
@Marcucciboy2 I got it working, I wasn't incrementing k before ending the IF `k = k + 1` Thanks very much for the help! — Nick, Aug 20 '18 at 17:08

score 1 · Answer 1 · answered Aug 23 '18 at 15:42

The problem you're running into is that you can't check if Date variables are "empty" using isEmpty() or even with Len() because the default value for a date is 30-Dec-1899 00:00:00, so there is always a value in a Date variable.

Instead, you should check to see that a Date variable is empty/has not been filled like this

If PPD_2_Date = 0 Then
...

IF logic for all date variables empty

1 Answers1