Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    398
    Location

    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 .

    Result.jpg
    Attached Files Attached Files

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    779
    Location
    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

  3. #3
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    398
    Location
    Dear Mana , Really you are Great . You saved me . Wow Thank you so much .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •