Match dates from sheet1 to sheet2, then copy data back to sheet1 to a different column

[danbates]

Please can someone help me?

On sheet1 I have 51 random dates in column B ranging from B7:B58.

On sheet2, I have a long list of dates starting from 01/01/2022 and ending 31/12/2030 (A1:A3287) in column A and in column B is the colour of our shift at work during them dates.

What I would like is a VBA code that can look for each date on sheet1 (B7:B58), and then match them with the date on sheet2. Once it has found the date on sheet2, it then copy’s the shift colour from column B on sheet2 and then pastes it in column C on sheet1 next to the original date.

Any help would be appreciated.

Thanks

Dan

 

[mohadin]

Hi
Try either

Sub test()
    Dim a: Dim i&
    a = Sheets("sheet1").Range("B7:B58")
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> 0 Then
                If Not .exists(a(i, 1)) Then .Item(a(i, 1)) = .Item(a(i, 1))
            End If
        Next
        a = Sheets("sheet2").Range("a1:a" & Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 2)
        For i = 1 To UBound(a)
            If .exists(a(i, 1)) Then .Item(a(i, 1)) = Sheets("Sheet2").Cells(i, 2).Interior.Color
        Next
        For i = 0 To .Count - 1
            Sheets("sheet1").Cells(7 + i, 3).Interior.Color = .Items()(i)
        Next
    End With
End Sub

Sub test2()
    Dim a: Dim i&
    a = Sheets("sheet1").Range("B7:B58")
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> 0 Then
                If Not .exists(a(i, 1)) Then .Item(a(i, 1)) = .Item(a(i, 1))
            End If
        Next
        a = Sheets("sheet2").Range("a1:a" & Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 2)
        For i = 1 To UBound(a)
            If .exists(a(i, 1)) Then .Item(a(i, 1)) = Sheets("Sheet2").Cells(i, 2)
        Next
        Sheets("sheet1").Cells(7, 3).Resize(.Count) = Application.Transpose(.Items)
    End With
End Sub

Depends the meaning of the shift color in column B

 

[danbates]

Hi, mohadin,

Your first code works perfectly, thank you so much.

Thanks

Dan

 

[mohadin]

You are very welcome
And thank you for the feedback
Be happy and safe