How to make VBA code excute time shorter and how to get correct output for mismatch condition for VBA code?

[Sherli]

Hi, everyone. I'm quite new in VBA. I have face 2 problem after I run my code. I have 2000+ row in my worksheet but the pic that I attach, below just a piece of my data.

(1) My code 1 is work but it take around 10 minutes. May I know is it got other way to make it run faster?
This is my code 1:

'Code 1
Sub FillSAintoraw()
    Dim wb2 As Workbook
    Dim A As String, ASp1 As String
    Dim xA As Long, yA As Long
    
    Set wb2 = Workbooks("jj")
    
    A = wb2.Worksheets("A1").Range("A" & Rows.Count).End(xlUp).Row
    ASp1 = wb2.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For xA = 2 To A
        COM = wb2.Worksheets("A1").Range("A" & xA) & wb2.Worksheets("A1").Range("B" & xA)
        For yA = 2 To ASp1
            If COM = wb2.Worksheets("Sheet1").Range("A" & yA) & wb2.Worksheets("Sheet1").Range("B" & yA) Then
                wb2.Worksheets("A1").Range("D" & xA).Value = wb2.Worksheets("Sheet1").Range("D" & yA)
            Exit For
            End If
        Next yA
    Next xA
End Sub

Output of code 1

(2) My code 2 is work too but the output is incorrect. The output of code 2 should be same with the output as code 1. May I know how can I solve it?
This is my code 2:

[/B]
'Code 2
Sub FillSAintoraw()
    Dim wb2 As Workbook
    Dim A As String, ASp1 As String
    Dim xA As Long, yA As Long
    
    Set wb2 = Workbooks("jj")
    
    A = wb2.Worksheets("A1").Range("A" & Rows.Count).End(xlUp).Row
    ASp1 = wb2.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For xA = 2 To A
        COM = wb2.Worksheets("A1").Range("A" & xA) & wb2.Worksheets("A1").Range("B" & xA) & wb2.Worksheets("A1").Range("D" & xA)
        For yA = 2 To ASp1
            If COM <> wb2.Worksheets("Sheet1").Range("A" & yA) & wb2.Worksheets("Sheet1").Range("B" & yA) & wb2.Worksheets("Sheet1").Range("D" & yA) Then
                wb2.Worksheets("A1").Range("D" & xA).Value = wb2.Worksheets("Sheet1").Range("D" & yA)
            Exit For
            End If
        Next yA
    Next xA
End Sub
[B]

Output of code 2

Thanks for the help.

 

[jolivanes]

It's always nice to see that people did try themselves. Kudos to you.
Just explain in a concise manner without leaving anything out what you want to achieve

BTW, you use "Dim" but you don't have "Option Explicit" at the top I suspect.

 

[Fluff]

How about

Sub Sherli()
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Txt As String
   
   Set Dic = CreateObject("scripting.dictionary")
   With Workbooks("JJ").Sheets("Sheet1")
      Ary = .Range("A2:D" & .Range("A" & Rows.Count).End(xlUp).Row)
   End With
   For i = 1 To UBound(Ary)
      Txt = Ary(i, 1) & "|" & Ary(i, 2)
      Dic(Txt) = Ary(i, 4)
   Next i
   With Workbooks("JJ").Sheets("A1")
      Ary = .Range("A2:D" & .Range("A" & Rows.Count).End(xlUp).Row)
      For i = 1 To UBound(Ary)
         Txt = Ary(i, 1) & "|" & Ary(i, 2)
         If Dic.Exists(Txt) Then Ary(i, 4) = Dic(Txt)
      Next i
      .Range("A2").Resize(UBound(Ary), 4).Value = Ary
   End With
End Sub

 

[Sherli]

The code is work nicely. Really thanks for your help, Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.