|
本帖最后由 我行我速2008 于 2022-7-25 17:22 编辑
Sub 对比()
Application.ScreenUpdating = False
Dim Ar, Br, Cr, R%, C%, X%, Y%, K%, I%, Sr1$, Sr2$, Sr3$
ClearContents
Ar = Sheets("表一").Range("A1").CurrentRegion
Br = Sheets("凭证表2020").Range("A1").CurrentRegion
For R = 2 To UBound(Ar)
Sr1 = Ar(R, 9)
K = 0
ReDim Cr(1 To UBound(Br, 2), 1 To 1)
For X = 2 To UBound(Br)
Sr2 = Br(X, 8): Sr3 = Br(X, 5)
If InStr(Sr2, Sr1) > 0 And Ar(R, 7) = Br(X, 12) Then
For I = 2 To UBound(Br)
If Br(I, 5) = Sr3 Then
K = K + 1
ReDim Preserve Cr(1 To UBound(Br, 2), 1 To K)
For Y = 1 To UBound(Br, 2)
Cr(Y, K) = Br(I, Y)
Next Y
End If
Next I
Sheets("表一").Cells(R, 1).Resize(1, 9).Copy Sheets("对比表2020").Range("A" & Sheets("对比表2020").Cells(Rows.Count, 10).End(xlUp).Row + 1)
Sheets("对比表2020").Range("J" & Sheets("对比表2020").Cells(Rows.Count, 10).End(xlUp).Row).Offset(1, 0).Resize(K, UBound(Br, 2)) = Application.Transpose(Cr)
GoTo 100
ElseIf InStr(Sr2, Sr1) > 0 And Ar(R, 7) = Br(X, 13) Then
For I = 2 To UBound(Br)
If Br(I, 5) = Sr3 Then
K = K + 1
ReDim Preserve Cr(1 To UBound(Br, 2), 1 To K)
For Y = 1 To UBound(Br, 2)
Cr(Y, K) = Br(X, Y)
Next Y
End If
Next I
Sheets("表一").Cells(R, 1).Resize(1, 9).Copy Sheets("对比表2020").Range("A" & Sheets("对比表2020").Cells(Rows.Count, 10).End(xlUp).Row + 1)
Sheets("对比表2020").Range("J" & Sheets("对比表2020").Cells(Rows.Count, 10).End(xlUp).Row).Offset(1, 0).Resize(K, UBound(Br, 2)) = Application.Transpose(Cr)
End If
Next X
100:
Next R
Application.ScreenUpdating = True
End Sub
Sub ClearContents()
Sheets("对比表2020").Range("A1").CurrentRegion.Offset(1).ClearContents
End Sub
|
|