Compare 2 Sheet and by 2 column and compare date get the value on sheet 1

Hello To All ,
I need a VBA That can compare Column B and C and F (Date) on Sheet 1 with the same range on sheet 2 and find the same value and the date that to be same or before the date on Sheet 1 . (Only one and if cannot find the date put error Error)
if you see the below image you can see it .

Attachment 21985

Code:

---

Option Explicit


Sub test()
    Dim r1 As Range
    Dim r2 As Range
    Dim wsT As Worksheet
    Dim i As Long, j As Long
    Dim s As String
    Dim d As Long
   
   
    Set r1 = Worksheets("Sheet1").Cells(1).CurrentRegion
    Worksheets("Sheet2").Copy
    Set wsT = ActiveSheet
    Set r2 = wsT.Cells(1).CurrentRegion
    r2.Sort r2.Columns(6), xlDescending


    For i = 1 To r1.Rows.Count
        r1.Cells(i, 9).Value = "Error"
        s = r1.Cells(i, 2).Value & r1.Cells(i, 3).Value
        d = r1.Cells(i, 6).Value2
        For j = 1 To r2.Rows.Count
            If s = r2.Cells(j, 2).Value & r2.Cells(j, 3).Value Then
                If d >= r2.Cells(j, 6).Value2 Then
                    r2.Rows(j).Copy r1.Cells(i, 9)
                    Exit For
                End If
            End If
        Next
    Next
   
    wsT.Parent.Close False
   
 End Sub

---

Dear Mana , Really you are Great . You saved me . Wow Thank you so much .