Need some assistance with this script...

[pujo]

I am trying to setup a Date/Time check for a workbook.
I am not getting any errors in my logic however, I just don't think this is correct.

In each If statement, I placed a check to see if the statement "If cDate" was working correct and not getting what I think I should.
I am getting the "Date Changed" value for each statement

Hope someone can validate my logic, and help correct.
Logic & Mini Sheet below:

Option Explicit

Sub ResetRow30()
    Rows("30:30").ClearContents
End Sub

Sub TimeCheck()
    Application.ScreenUpdating = True
    Dim Rng         As Range
'CurrDayR
    Set Rng = Sheets(1).Range("$D$26")
    If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) > Rng Then
        Sheets(1).Range("$D$2") = "=(TODAY()-1)+TIMEVALUE(""07:00:00"")"
        Sheets(1).Range("CurrDayR").Calculate
        Sheets(1).Range("$D$30").Value = "Date Changed"
        Set Rng = Nothing
    End If
'CurrDayV
    Set Rng = Sheets(1).Range("$F$3")
    If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) > Rng Then
        Sheets(1).Range("CurrDayV").Calculate
        Sheets(1).Range("$F$30").Value = "Date Changed"
        Set Rng = Nothing
    End If
'PrevDayR
    Set Rng = Sheets(1).Range("$H$26")
    If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) - 1 > Rng Then
        Sheets(1).Range("$H$2") = "=(TODAY()-2)+TIMEVALUE(""07:00:00"")"
        Sheets(1).Range("PrevDayR").Calculate
        Sheets(1).Range("$H$30").Value = "Date Changed"
        Set Rng = Nothing
    End If
'PrevDayV
    Set Rng = Sheets(1).Range("$J$3")
    If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) - 1 > Rng Then
        Sheets(1).Range("PrevDayV").Calculate
        Sheets(1).Range("$J$30").Value = "Date Changed"
        Set Rng = Nothing
    End If
'CurrMonthV
    Set Rng = Sheets(1).Range("$L$3")
    If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) + 1 > Rng Then
        Sheets(1).Range("$L$2") = "=EOMONTH(TODAY(),-1)+1+ TIMEVALUE(""07:00:00"")"
        Sheets(1).Range("$L$3") = "=NOW()"
        Sheets(1).Range("CurrMonthV").Calculate
        Sheets(1).Range("$L$30").Value = "Date Changed"
        Set Rng = Nothing
    End If
'PrevMonthV
    Set Rng = Sheets(1).Range("$N$3")
    If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) + 1 > Rng Then
        Sheets(1).Range("$N$2") = "=EOMONTH(TODAY(),-2)+1+ TIMEVALUE(""07:00:00"")"
        Sheets(1).Range("$N$3") = "=EOMONTH(TODAY(),-1)+1+ TIMEVALUE(""07:00:00"")"
        Sheets(1).Range("PrevMonthV").Calculate
        Sheets(1).Range("$N$30").Value = "Date Changed"
        Set Rng = Nothing
    End If
    Sheets(1).Range("TimeStamp") = "=Now()"
    Application.ScreenUpdating = True
End Sub

TimeCheck.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		TimeStamp		CurrentDayR		CurrentDayV		PreviousDayR		PreviousDayV		CurrentMonthV		PreviousMonthV
2		18-Sep-22 12:43:15		17-Sep-22 07:00:00		17-Sep-22 07:00:00		16-Sep-22 07:00:00		16-Sep-22 07:00:00		1-Sep-22 07:00:00		1-Aug-22 07:00:00
3				17-Sep-22 08:00:00		18-Sep-22 12:43:15		16-Sep-22 08:00:00		17-Sep-22 07:00:00		18-Sep-22 12:43:15		1-Sep-22 07:00:00
4				17-Sep-22 09:00:00				16-Sep-22 09:00:00
5				17-Sep-22 10:00:00				16-Sep-22 10:00:00
6				17-Sep-22 11:00:00				16-Sep-22 11:00:00
7				17-Sep-22 12:00:00				16-Sep-22 12:00:00
8				17-Sep-22 13:00:00				16-Sep-22 13:00:00
9				17-Sep-22 14:00:00				16-Sep-22 14:00:00
10				17-Sep-22 15:00:00				16-Sep-22 15:00:00
11				17-Sep-22 16:00:00				16-Sep-22 16:00:00
12				17-Sep-22 17:00:00				16-Sep-22 17:00:00
13				17-Sep-22 18:00:00				16-Sep-22 18:00:00
14				17-Sep-22 19:00:00				16-Sep-22 19:00:00
15				17-Sep-22 20:00:00				16-Sep-22 20:00:00
16				17-Sep-22 21:00:00				16-Sep-22 21:00:00
17				17-Sep-22 22:00:00				16-Sep-22 22:00:00
18				17-Sep-22 23:00:00				16-Sep-22 23:00:00
19				18-Sep-22 00:00:00				17-Sep-22 00:00:00
20				18-Sep-22 01:00:00				17-Sep-22 01:00:00
21				18-Sep-22 02:00:00				17-Sep-22 02:00:00
22				18-Sep-22 03:00:00				17-Sep-22 03:00:00
23				18-Sep-22 04:00:00				17-Sep-22 04:00:00
24				18-Sep-22 05:00:00				17-Sep-22 05:00:00
25				18-Sep-22 06:00:00				17-Sep-22 06:00:00
26				18-Sep-22 07:00:00				17-Sep-22 07:00:00
27
28
29
30				Date Changed		Date Changed		Date Changed		Date Changed		Date Changed		Date Changed
TimeCheck

Cell Formulas
Range	Formula
B2,L3,F3	B2	=NOW()
F2,D2	F2	=(TODAY()-1)+TIMEVALUE("07:00:00")
J2	J2	=TODAY()-3+1+TIMEVALUE("07:00:00")
J3	J3	=TODAY()-2+1+TIMEVALUE("07:00:00")
L2,N3	L2	=EOMONTH(TODAY(),-1)+1+ TIMEVALUE("07:00:00")
N2	N2	=EOMONTH(TODAY(),-2)+1+ TIMEVALUE("07:00:00")
D3:D26	D3	=$D2+TIME(1,0,0)
H2	H2	=(TODAY()-2)+TIMEVALUE("07:00:00")
H3:H26	H3	=$H2+TIME(1,0,0)

Named Ranges
Name	Refers To	Cells
CurrDayR	=TimeCheck!$D$2:$D$26	D3
PrevDayR	=TimeCheck!$H$2:$H$26	H3

 

[Micron]

I am getting the "Date Changed" value for each statement

That suggests the IF test is true, so no idea what this means

If cDate" was working correct and not getting what I think I should.

Did you step thru code and verify values? Can't tell what you issue is they way you describe it.

 

[pujo]

That suggests the IF test is true, so no idea what this means

Did you step thru code and verify values? Can't tell what you issue is they way you describe it.

Thanks for replying!

The goal is to not update if cDate is NOT greater than Rng.
When I step through the logic, it doesn't step over the if statement that I believe are false, and puts the "Date Change" in row 30.
Like the "PrevDay", "PrevMonth" columns.

Maybe my test isn't right, or my code is not right but...
When I get this working the way I think it should, I will add more lines in the IF statement for calculations.
Skip the calculations if the cDate statements are false.

Hope that makes sense...

Thanks!

 

[OaklandJim]

Pujo

For me at least it is not clear what you are trying to accomplish. Tell us more about the logic that you are trying to apply and the desired result.

I would also suggest that you add some comments to your code to specify what is supposed to happen. Do it for each if statement. That way we can tell what each one is supposed to do. Explain what these mean: CurrDayR, CurrDayV, PrevDayR, PrevDayV, CurrMonthV, PrevMonthV

The code below is a bit tighter, which makes reading it a bit easier.

Sub TimeCheck()
    
    Dim Rng As Range
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.Sheets(1)
    
        .Rows("30:30").ClearContents
        
'       CurrDayR
        Set Rng = .Range("$D$26")
        If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) > Rng Then
            .Range("$D$2").Formula = "=(TODAY()-1)+TIMEVALUE(""07:00:00"")"
            .Range("$D$30") = "Date Changed"
        End If
        
'       CurrDayV
        Set Rng = .Range("$F$3")
        If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) > Rng Then
            .Range("$F$30") = "Date Changed"
        End If
        
'       PrevDayR
        Set Rng = .Range("$H$26")
        If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) - 1 > Rng Then
            .Range("$H$2").Formula = "=(TODAY()-2)+TIMEVALUE(""07:00:00"")"
            .Range("$H$30") = "Date Changed"
        End If
        
'       PrevDayV
        Set Rng = .Range("$J$3")
        If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) - 1 > Rng Then
             .Range("$J$30") = "Date Changed"
        End If
        
'       CurrMonthV
        Set Rng = .Range("$L$3")
        If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) + 1 > Rng Then
            .Range("$L$2").Formula = "=EOMONTH(TODAY(),-1)+1+ TIMEVALUE(""07:00:00"")"
            .Range("$L$3").Formula = "=NOW()"
            .Range("$L$30") = "Date Changed"
        End If
        
'       PrevMonthV
        Set Rng = .Range("$N$3")
        If CDate(Format(Now, "d-mmm-yy hh:mm:ss")) + 1 > Rng Then
            .Range("$N$2").Formula = "=EOMONTH(TODAY(),-2)+1+ TIMEVALUE(""07:00:00"")"
            .Range("$N$3").Formula = "=EOMONTH(TODAY(),-1)+1+ TIMEVALUE(""07:00:00"")"
            .Range("$N$30") = "Date Changed"
        End If
        
        .Range("B2").Formula = "=Now()"
    
    End With  'ThisWorkbook.Sheets(1)
    
    Set Rng = Nothing
    
    Application.ScreenUpdating = True
    
End Sub