Excel VBA code with if condition and date

[adnan1975]

Hi,
I need help with the following VBA code

For i = 5 To 750
If Cells(i, 19) = "" Or Cells(i, 39) = "Y" Then
Cells(i, 44) = ""
ElseIf Cells(i, 19).Value + 60 > 7 / 31 / 2017 Then
Cells(i, 44) = "OVERDUE"
Else: Cells(i, 44) = "NOT OVERDUE"
End If
Next i

This code is part of procedure which I am using. The intent here is that cell has a date and i want to add 60 days in the date and call it "OVERDUE" if it exceeds specified date in the code. The code runs fine but gives me all cells in 750 row column "OVERDUE" which is not correct because some of the dates are in future and cant be overdue. i am not sure if there is something wrong with the date format or the code itself.
Also, i am using 750 rows which is more than what I need. How do I make sure that code only goes to the last data row and stop?

Any help is much appreciated.

Thanks

 

[JPGKIll3R]

Hi,
I need help with the following VBA code

For i = 5 To 750
If Cells(i, 19) = "" Or Cells(i, 39) = "Y" Then
Cells(i, 44) = ""
ElseIf Cells(i, 19).Value + 60 > 7 / 31 / 2017 Then
Cells(i, 44) = "OVERDUE"
Else: Cells(i, 44) = "NOT OVERDUE"
End If
Next i

This code is part of procedure which I am using. The intent here is that cell has a date and i want to add 60 days in the date and call it "OVERDUE" if it exceeds specified date in the code. The code runs fine but gives me all cells in 750 row column "OVERDUE" which is not correct because some of the dates are in future and cant be overdue. i am not sure if there is something wrong with the date format or the code itself.
Also, i am using 750 rows which is more than what I need. How do I make sure that code only goes to the last data row and stop?

Any help is much appreciated.

Thanks

Hi, Adnan -

Please test this code on a copy of your worksheet.
As your comparison point is currently being handled as a fixed value, I would suggest to add it into the worksheet, so the code will have a dynamic variant, instead than a value.

Sub MacroTest()


Dim ws As Worksheet
Dim wb As Workbook


Set wb = ActiveWorkbook
Set ws = wb.Worksheets("[COLOR=#ff0000]Sheet1[/COLOR]")


Dim tDate As Variant




With ws
tDate = ws.Range("[COLOR=#ff0000]B4[/COLOR]").Value


    lrow = ws.Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp).Row
    For i = 5 To lrow
        If Cells(i, 19) = "" Or Cells(i, 39) = "Y" Then
            Cells(i, 44).Value = ""
        ElseIf Cells(i, 19).Value + 60 > tDate Then
            Cells(i, 44) = "Overdue"
        Else: Cells(i, 44) = "Not Overdue"
        End If
    Next i
End With
End Sub

I added the comparison date ( 7 / 31 / 2017) into cell B4, so please make sure to update the cell with your data.

 

[adnan1975]

Hi, Adnan -

Please test this code on a copy of your worksheet.
As your comparison point is currently being handled as a fixed value, I would suggest to add it into the worksheet, so the code will have a dynamic variant, instead than a value.

Sub MacroTest()


Dim ws As Worksheet
Dim wb As Workbook


Set wb = ActiveWorkbook
Set ws = wb.Worksheets("[COLOR=#ff0000]Sheet1[/COLOR]")


Dim tDate As Variant




With ws
tDate = ws.Range("[COLOR=#ff0000]B4[/COLOR]").Value


    lrow = ws.Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp).Row
    For i = 5 To lrow
        If Cells(i, 19) = "" Or Cells(i, 39) = "Y" Then
            Cells(i, 44).Value = ""
        ElseIf Cells(i, 19).Value + 60 > tDate Then
            Cells(i, 44) = "Overdue"
        Else: Cells(i, 44) = "Not Overdue"
        End If
    Next i
End With
End Sub

I added the comparison date ( 7 / 31 / 2017) into cell B4, so please make sure to update the cell with your data.

Thanks so much. it worked but I had to change date to DATE from VARIANT. Hope i am doing it correctly.
once again thanks a lot.