CODEWITHSUNDEEP
excelvba
Pierre Bonaparte
Pierre Bonaparte

Reputation: 633

Identify day of the week and time constrain and loop through the range

I'm trying to loop through a specific range to identify if the date + time (each cell format is dd/mm/yyyy HH:mm:ss) equals a specific day of the week and is between specific time.

Each cell has a different date and time, so I guess Time = each cell in D looped one by one?

Sub looper()
    
    Dim Time As Date, cel As Range
    Time = ThisWorkbook.Sheets("Test1").Range("D1")
    
    For Each cel In Range("D1:D26")
        If IsEmpty(cel.Value) Then Exit For
            
        If Weekday(ThisWorkbook.Sheets("Test1").Range("D1")) = 5 And _
          Time < TimeValue("17:59:59") And _
          Time > TimeValue("06:00:00") _
          Then cel.Offset(0, 1).Value = "yes"
    
    Next
    
End Sub

Upvotes: 0

Views: 44

Answers (2)

Daniel Bellmas
Daniel Bellmas

Reputation: 657

try this, hope it helps :)

Sub looper()

Dim Time As Date, cel As Range

For Each cel In Range("D1:D26")
    Time = cel.Value
    If Not IsEmpty(Time) Then
        If Weekday(Time) = 5 And TimeValue(Time) < TimeValue("17:59:59") And TimeValue(Time) > TimeValue("06:00:00") _
        Then cel.Offset(0, 1).Value = "yes"
    End If

Next

End Sub

Upvotes: 1

Алексей Р
Алексей Р

Reputation: 7627

If I understand the problem correctly, it can be implemented as follows:

Option Explicit

Sub looper()
    Dim cel As Range
    
    Dim Time As Date, minTime As Date, maxTime As Date
    minTime = TimeSerial(6, 0, 0)
    maxTime = TimeSerial(17, 59, 59)
    
    With ThisWorkbook.Sheets("Test1")
        For Each cel In Range("D1:D26")
            If IsEmpty(cel) Then Exit For
            Time = TimeValue(cel)   'extract time
            If Weekday(cel) = 5 And Time > minTime And Time < maxTime Then
                cel.Offset(0, 1) = "yes"
            End If
        Next
    End With
End Sub

enter image description here

Upvotes: 1

Related Questions