PDA

View Full Version : [SOLVED] Compare 2 Sheet and by 2 column and compare date get the value on sheet 1



parscon
04-06-2018, 08:34 AM
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 .

21985

mana
04-06-2018, 06:31 PM
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

parscon
04-06-2018, 11:18 PM
Dear Mana , Really you are Great . You saved me . Wow Thank you so much .