找不到为什么我的代码崩溃了

I wrote this code to compare between A and B columns and whenever I have similar values like if the column A = B then show A, B, and C in E,F, and G like in the picture below :

我写了这段代码来比较A和B列,每当我有类似的值时,如果列A = B,则在E,F和G中显示A,B和C,如下图所示:

This is the code I am using:

这是我正在使用的代码:

Option Explicit

Sub Comparatif_Release()
Dim t1, t2, c
Dim d As Object
Dim i&, j&, l&
Dim f As Worksheet
Range("e1:g110000").Select
Selection.ClearContents

Set f = Sheets("Sheet1")
With f

    t1 = .Range("a1:a10000").Value
    t2 = .Range("b1:c10000").Value
End With

Set d = CreateObject("Scripting.Dictionary")
For i = LBound(t1) To UBound(t1)
For j = LBound(t2) To UBound(t2)
    If t1(i, 1) = t2(j, 1) Then
        d(t1(i, 1) & ":" & t2(j, 1) & ":" & t2(j, 2)) = d(t1(i, 1) & ":" & t2(j, 1) & ":" & t2(j, 2))

    End If
Next j
Next i

With f
    i = 1
    For Each c In d.Keys
        .Cells(i, "E").Resize(, 3).Value = Split(c, ":")

        i = i + 1
    Next c
End With

End Sub

3 个解决方案

#1

Use these formula and just filter them down. It should work. Late post, but just in case.

使用这些公式,只需将其过滤掉即可。它应该工作。晚了,但以防万一。

In column E1 :  =IF(A1=B1,A1,"")
In column F1 :  =IF(E1<>"",B1,"")
In Column G1 :  =If(E1<>"",C1,"")

#2

A simple VBA version would be:

一个简单的VBA版本是:

Private Sub CommandButton1_Click()
Dim i As Long
lastrow = Sheet1.Range("a1048575").End(xlUp).Row
For i = 1 To lastrow
    If Sheet1.Cells(i, 2) = Sheet1.Cells(i, 1) Then
        Sheet1.Cells(i, 4) = Sheet1.Cells(i, 1)
        Sheet1.Cells(i, 5) = Sheet1.Cells(i, 2)
        Sheet1.Cells(i, 6) = Sheet1.Cells(i, 3)
    End If
Next
End Sub

#3

What about this part when you say then d()=d() ? Where both keys are seemingly the same and might not exist?

当你说d()= d()时,这个部分怎么样?哪两个键看起来都一样,可能不存在?

Full quote is:

完整报价是:

If t1(i, 1) = t2(j, 1) Then d(t1(i, 1) & ":" & t2(j, 1) & ":" & t2(j, 2)) = d(t1(i, 1) & ":" & t2(j, 1) & ":" & t2(j, 2))

#1