Dear Experts,
Request your support on the below where I was trying to figure out the issue with the code and could not succeed.
Attached the sample file where I wanted to combine the values in column A & B in both the sheets and compare and take the unique values from column F of Sheet("Detailed") to column F of Sheet("Summary").
Sheet("Detailed") - Invoice numbers repeating with either same and/or different rejection remarks.
Sheet("Summary")- Pivoted by CompanyName and InvoiceNumber
I have done it using the filter formula in column G of Sheet("Summary"). But I wanted the same to be done using the VBA code and seeking help in correcting and guiding what I'm doing wrong.
Thanks,
Sub Check()
Dim Sh1 As Worksheet, Sh2 As Worksheet
Dim Sh1_LR As Long, Sh2_LR As Long, x As Long, y As Long
Dim rng1, rng2, rng3
Dim dict
Dim rngResults As Range
Set Sh1 = Sheets("Summary")
Set Sh2 = Sheets("Detailed")
Sh1_LR = Sh1.UsedRange.Rows.Count
Sh2_LR = Sh2.UsedRange.Rows.Count
Set rngResults = Sh1.Range("F2:F" & Sh1_LR)
ReDim Results(1 To Sh1_LR - 1, 1 To 1)
rng1 = Sh1.Range("A2:A" & Sh1_LR).Value & Sh1.Range("B2:B" & Sh1_LR).Value
rng2 = Sh2.Range("A2:A" & Sh2_LR).Value & Sh2.Range("B2:B" & Sh2_LR).Value
rng3 = Sh2.Range("F2:F" & Sh2_LR).Value
Set dict = CreateObject("Scripting.Dictionary")
For x = 1 To UBound(rng1)
dict.RemoveAll
For y = 1 To UBound(rng2)
If rng1(x) = rng2(y) Then
dict(rng3(y, 1)) = 0
End If
Next y
If dict.Count > 0 Then Results(x, 1) = Join(dict.keys, " | ")
Next x
rngResults.Value = Results
Erase rng1, rng2, Results
MsgBox "DONE"
End Sub