VBA Calculate date difference

[cortexnotion]

Hi All,

Below is an extract from my current code. After some formulas have been inserted I need to perform the following calculations. Currently, the first condition does not add anything into Column M?

- If Column A does not contains "TCS", Column D has a valid date, Column E is empty and Column F is empty, Then in Column M do Workday Column L Value + 10

OR

- If Column A does contains "TCS", Column D has a valid date, Column E is empty and Column F is empty, Then in Column M add Column L Value.

*Column D,E,F all have formula which returns a date or enters "" in the cell

With Sheets("DataWork")
.Range("AB3:AB" & LR2).Formula = "=IF(ISNUMBER(E3),IF(ISNUMBER(SEARCH(""TCS"",$A3)),WORKDAY(E3,-" & 0 & ",FigWork!$AE$2:$AE$9),WORKDAY(E3,-" & 5 & ",FigWork!$AE$2:$AE$9)),"""")"
    .Range("AC3:AC" & LR2).Formula = "=IF(ISNUMBER(F3),F3,"""")"
    .Range("AD3:AD" & LR2).Formula = "=IF(ISNUMBER(G3),IF(ISNUMBER(SEARCH(""TCS"",$A3)),WORKDAY(G3,-" & 5 & ",FigWork!$AE$2:$AE$9),WORKDAY(G3,-" & 5 & ",FigWork!$AE$2:$AE$9)),"""")"
    .Range("AE3:AE" & LR2).Formula = "=IF(ISNUMBER(H3),IF(ISNUMBER(SEARCH(""TCS"",$A3)),WORKDAY(H3,-" & 20 & ",FigWork!$AE$2:$AE$9),WORKDAY(H3,-" & 10 & ",FigWork!$AE$2:$AE$9)),"""")"
    .Range("AF3:AF" & LR2).Formula = "=IF(ISNUMBER(I3),IF(ISNUMBER(SEARCH(""TCS"",$A3)),WORKDAY(I3,-" & 5 & ",FigWork!$AE$2:$AE$9),WORKDAY(I3,-" & 5 & ",FigWork!$AE$2:$AE$9)),"""")"
    .Range("AG3:AG" & LR2).Formula = "=IF(ISNUMBER(J3),IF(ISNUMBER(SEARCH(""TCS"",$A3)),WORKDAY(J3,-" & 5 & ",FigWork!$AE$2:$AE$9),WORKDAY(J3,-" & 5 & ",FigWork!$AE$2:$AE$9)),"""")"
    .Range("AH3:AH" & LR2).Formula = "=IF(ISNUMBER(K3),IF(ISNUMBER(SEARCH(""TCS"",$A3)),WORKDAY(K3,-" & 5 & ",FigWork!$AE$2:$AE$9),WORKDAY(K3,-" & 5 & ",FigWork!$AE$2:$AE$9)),"""")"

    For r = 3 To LR2
        If InStr(Cells(r, 1), "TCS") = 0 And Len(Cells(r, 4)) > 0 And Cells(r, 5) = "" And Cells(r, 6) = "" And _
        Application.Evaluate("NETWORKDAYS(L" & r & ",TODAY(),FigWork!$AE$2:$AE$9)-SIGN(NETWORKDAYS(L" & r & ",TODAY(),FigWork!$AE$2:$AE$9))") > 10 Then
            Cells(r, 13).Value = Application.Evaluate("WORKDAY(L" & r & "," & 10 & ",FigWork!$AE$2:$AE$9),"""")")
        ElseIf InStr(Cells(r, 1), "TCS") > 0 And Cells(r, 4) > 0 And Cells(r, 5) = "" And Cells(r, 6) = "" Then
            Cells(r, 13).Value = Cells(r, 12).Value
    End If
    Next r

End With

Many thanks!