Fill Employee name to specific cells if status is "Vacation Leave"

[chesterrae]

Hi All,

I need some guidance and ideas on how to Fill each Employee names if their status is "Vacation Leave" to the corresponding date of the calendar sheets.


For example on the image below, In row 3, Robert Downey Jr. and Chris Evans should be filled to January sheet (cell G8 and G9) in its corresponding date (which is January 2) since their status is Vacation Leave.


This is my raw data:

This are the calendar sheets where the Employee names should be filled:

Please help me, any ideas or codes will be really appreciated.

Thank you so much!

 

[My Aswer Is This]

Try this:

Run this script from Sheet Raw

Sub Vacation_Leave()
'Modified  8/7/2018  11:06:06 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim r As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Dim Lastrowa As Long
Dim d As String
    For i = 2 To Lastrow
        If Cells(i, "D").Value = "Vacation Leave" Then
            m = Format(Cells(i, "B").Value, "MMMM")
            d = Format(Cells(i, "B").Value, "D")
        End If
        For Each r In Sheets(m).UsedRange
            If r.Value = d Then
                Lastrowa = Sheets(m).Cells(Rows.Count, r.Column).End(xlUp).Row + 1
                Sheets(m).Cells(Lastrowa, r.Column).Value = Cells(i, "C").Value
            End If
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

After checking my script a second or third time I now find it will not work properly.
Will look into seeing if I can get it to work properly. It works properly for first time it finds a date in a months week but not if there is a date for second week in same month.

 

[NoSparks]

Maybe this ?

Sub Vacation_Leave_test()
    Dim src As Worksheet, rng As Range, cel As Range
    Dim calMnth As String, calDay As Integer
    Dim dayOff As Range, i As Integer

Set src = Sheets("Raw")
Set rng = src.Range("C2", src.Range("C" & Rows.Count).End(xlUp))

For Each cel In rng
    If cel.Offset(, 1).Value = "Vacation Leave" Then
        calMnth = Format(DateValue(cel.Offset(, -1).Value), "mmmm")
        calDay = Format(DateValue(cel.Offset(, -1).Value), "d")
        
        With Sheets(calMnth)
            Set dayOff = .Cells.Find(What:=calDay, _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByColumns, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
            If Not dayOff Is Nothing Then
                For i = 1 To 4
                    If dayOff.Offset(i).Value = "" Then
                        dayOff.Offset(i).Value = cel.Value
                        Exit For
                    End If
                Next i
            End If
        End With
        
    End If
Next cel
        
End Sub

 

[chesterrae]

Thank you guys for the response.

@NoSparks, your code is working perfectly! Brilliant! Thank you so much!


Have a great day ahead!

Maybe this ?

Sub Vacation_Leave_test()
    Dim src As Worksheet, rng As Range, cel As Range
    Dim calMnth As String, calDay As Integer
    Dim dayOff As Range, i As Integer

Set src = Sheets("Raw")
Set rng = src.Range("C2", src.Range("C" & Rows.Count).End(xlUp))

For Each cel In rng
    If cel.Offset(, 1).Value = "Vacation Leave" Then
        calMnth = Format(DateValue(cel.Offset(, -1).Value), "mmmm")
        calDay = Format(DateValue(cel.Offset(, -1).Value), "d")
        
        With Sheets(calMnth)
            Set dayOff = .Cells.Find(What:=calDay, _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByColumns, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
            If Not dayOff Is Nothing Then
                For i = 1 To 4
                    If dayOff.Offset(i).Value = "" Then
                        dayOff.Offset(i).Value = cel.Value
                        Exit For
                    End If
                Next i
            End If
        End With
        
    End If
Next cel
        
End Sub