For Each targetDate In targetRange ' selects first date in range in target ws
For Each sourceInDate In sourceRange ' selects first date in range in source ws
' step 1 - does InDate.source (sourceSheet.Range("K2").Value) = Date.target (targetSheet.Range("A14").Value)
If sourceInDate.Value = targetDate.Value Then ' if match goto step 2
' step 2 - does InDate.source (sourceSheet.Range("K2").Value) = OutDate.source on same row (sourceSheet.Range("T2").Value)
If sourceInDate.Value = sourceInDate.Offset(0, 9).Value Then ' if match goto step 3 ' step 3 - does InDate.source = PreviousRow.InDate.source
If sourceInDate.Value <> sourceInDate.Offset(-1, 0).Value Then ' if true, meaning NOT equal, goto step 3a
' step 3a - compare OutTime.source (sourceInDate.Offset(0, 12).Value) > InTime.source (sourceInDate.Offset(0, 3).Value)
If sourceInDate.Offset(0, 12).Value > sourceInDate.Offset(0, 3).Value Then ' if true
' copy same row InTime.source to InTime.target
targetDate.Offset(0, 1).Value = sourceInDate.Offset(0, 3).Value
' copy OutTime.source to LunchOut.target
targetDate.Offset(0, 3).Value = sourceInDate.Offset(0, 12).Value
' Call outNote (placeholder)
Else ' step 3a - because OutTime.source > InTime.source is false
' Call outNote (placeholder)
End If ' step 3a
Else ' step 3 - b/c InDate.source = PreviousRow.InDate.source is false, meaning they ARE equal, goto step 3b
' step 3b - compare OutTime.source > InTime.source
If sourceInDate.Offset(0, 12).Value > sourceInDate.Offset(0, 3).Value Then ' if true goto step 3b1
' step 3b1 - compare CurrentRow.InTime.source > PreviousRow.OutTime.source
If sourceInDate.Offset(0, 3).Value > sourceInDate.Offset(-1, 12).Value Then ' if true
' copy same row InTime.source to LunchIn.target
targetDate.Offset(0, 5).Value = sourceInDate.Offset(0, 3).Value
' copy OutTime.source to TimeOut.target
targetDate.Offset(0, 7).Value = sourceInDate.Offset(0, 12).Value
' Call outNote (placeholder)
End If ' step 3b1
' step 3b2 - does CurrentRow.InTime.source = PreviousRow.outTime.source
If sourceInDate.Offset(0, 3).Value = sourceInDate.Offset(-1, 12).Value Then ' if true
' copy OutTime.source to LunchOut.target (replacing previous LunchOut value)
targetDate.Offset(0, 3).Value = sourceInDate.Offset(0, 12).Value
' Call outNote (placeholder)
End If ' step 3b2
End If ' step 3b
End If ' step 3
Else ' step 2 - b/c InDate.source = OutDate.source on same row is false, goto step 2a
' step 2a - test if OutDate.source is a valid date
If IsDate(sourceInDate.Offset(0, 9).Value) = False Then ' if NOT valid date
MsgBox "OutDate is NOT valid date" ' DELETE temporary msg when done testing
' Call outNote (placeholder)
'
'[outNote]
If sourceInDate.Offset(0, 15).Value <> "" Then
If sourceInDate.Offset(0, 15).Value = "V" Or sourceInDate.Offset(0, 15).Value = "v" Then
targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
ElseIf sourceInDate.Offset(0, 15).Value = "S" Then
targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
ElseIf sourceInDate.Offset(0, 15).Value = "P" Then
targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
ElseIf sourceInDate.Offset(0, 15).Value = "F" Then
targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
ElseIf sourceInDate.Offset(0, 15).Value = "H" Then
targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
ElseIf sourceInDate.Offset(0, 15).Value = "L" Then
targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 18).Value * -1 ' copy time in adjLunch.source to OTadjHours.target
End If
End If
'[/outNote]
'
End If ' step 2a
End If ' step 2
End If ' step 1
Next sourceInDate ' move down to check the next date in date.source column
Next targetDate ' move down to check the next date in date.target column
Bookmarks