CODEWITHSUNDEEP
excelif-statementvba
user1665355
user1665355

Reputation: 3393

Incorrect execution inside if else loop

I have the following code. But the if-else loop executes not correctly. I.e. MsgBox (Hour(strTime)) prints 14,

So If (Hour(strTime) = 17 & Minute(time) = 31 & Weekday(time) <> 1 & Weekday(time) <> 2) should not execute. But it does it anyway every time. Appreciate if someone see any mistakes in the code. Best Regards

Private RunWhen As Date
Private Const StartTime As Date = #2:14:30 PM#
Private Const EndTime As Date = #5:34:55 PM#
Private Const cRunInterval = "00:01:00"
Private Const cRunWhat = "Data" ' the name of the procedure to run


Sub StartTimer()
Dim strTime
strTime = time

    If RunWhen = 0 Then
        RunWhen = StartTime
    Else
        RunWhen = RunWhen + TimeValue(cRunInterval)
    End If
    MsgBox (Hour(strTime))
    If (RunWhen <= EndTime & Weekday(time) <> 1 & Weekday(time) <> 2) Then
        If (Hour(strTime) = 17 & Minute(time) = 31 & Weekday(time) <> 1 & Weekday(time) <> 2) Then
            'MsgBox ("Mail")
            Call CDO_Mail_Small_Text
        Else
            If (Hour(strTime) = 17 & Minute(time) = 33 & Weekday(time) <> 1 & Weekday(time) <> 2) Then
                MsgBox ("clear")
                Call ClearData
            Else
            Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
            End If
        End If
    End If


End Sub

Sub Data()
Dim RowNo As Long
Dim RowNo2 As Long
RowNo = Sheets(1).Cells(Rows.Count, 11).End(xlUp).Row
RowNo2 = Sheets(1).Cells(Rows.Count, 14).End(xlUp).Row + 1
    Sheets(1).Cells(RowNo2, 15) = Sheets(1).Cells(RowNo, 11) 
    Sheets(1).Cells(RowNo2, 16) = Sheets(1).Cells(RowNo, 12) 
    Sheets(1).Cells(RowNo2, 17) = Sheets(1).Cells(RowNo, 13) 
    Sheets(1).Cells(RowNo2, 14) = time

    StartTimer ' Reschedule the procedure
End Sub

Upvotes: 0

Views: 31

Answers (1)

Brandon Barney
Brandon Barney

Reputation: 2392

I believe your issues is with using & instead of the boolean operator AND.

Sub StartTimer()
Dim strTime
strTime = time

If RunWhen = 0 Then
    RunWhen = StartTime
Else
    RunWhen = RunWhen + TimeValue(cRunInterval)
End If
MsgBox (Hour(strTime))
If (RunWhen <= EndTime AND Weekday(time) <> 1 AND Weekday(time) <> 2) Then
    If (Hour(strTime) = 17 AND Minute(time) = 31 AND Weekday(time) <> 1 AND Weekday(time) <> 2) Then
        'MsgBox ("Mail")
        Call CDO_Mail_Small_Text
    Else
        If (Hour(strTime) = 17 AND Minute(time) = 33 AND Weekday(time) <> 1 & Weekday(time) <> 2) Then
            MsgBox ("clear")
            Call ClearData
        Else
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
        End If
    End If
End If


End Sub

Also, I am not sure if you are calling this sub with arguments from another routine, but when I tested it I was getting numerous variables that weren't defined.

It is good practice to ensure Option Explicit is declared at the beginning of your module.

This ensures all variables are declared and it has saved me from countless hours of tracing back variables due to typos.

Upvotes: 3

Related Questions