|
发表于 2014-12-16 08:43
|
显示全部楼层
本楼为最佳答案
- Sub MatchAnB()
- Dim d1, arr, brr, mrr, RN&, n&, aRN&, Amt#, ttt
- ttt = Timer
- With Application
- .ScreenUpdating = False
- .EnableEvents = False
- .DisplayAlerts = False
- .Calculation = xlCalculationManual
- End With
-
- Set d1 = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("A1:D" & Sheet1.Cells(Rows.Count, 1).End(3).Row)
- brr = Sheet2.Range("A1:D" & Sheet2.Cells(Rows.Count, 1).End(3).Row)
- For RN = 3 To UBound(arr)
- Amt = arr(RN, 3)
- d1(Amt) = d1(Amt) & "," & RN
- Next
- For RN = 3 To UBound(brr)
- Amt = brr(RN, 3)
- If d1.exists(Amt) Then
- mrr = Split(d1(Amt), ",")
- If UBound(mrr) >= 1 Then
-
- If brr(RN, 2) <> "" Then
- For n = 1 To UBound(mrr)
- aRN = Val(mrr(n))
- If aRN > 0 Then
- If brr(RN, 2) = arr(aRN, 2) Then
- brr(RN, 4) = arr(aRN, 1)
- arr(aRN, 4) = brr(RN, 1)
- d1(Amt) = Replace(d1(Amt) & ",", "," & aRN & ",", ",")
- GoTo aa
- End If
- End If
- Next
- End If
-
- End If
- End If
- aa:
- Next
- Sheet1.[d1].Resize(UBound(arr), 1) = Application.Index(arr, , 4)
- Sheet2.[d1].Resize(UBound(brr), 1) = Application.Index(brr, , 4)
-
- With Sheet1.Range("A2:D" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row)
- .AutoFilter Field:=4, Criteria1:="<>"
- .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)
- .AutoFilter
- End With
- With Sheet2.Range("A2:D" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row)
- .AutoFilter Field:=4, Criteria1:="<>"
- .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Sheet3.Range("F" & Rows.Count).End(xlUp).Offset(1)
- .AutoFilter
- End With
- With Sheet3
- .Range("A3:D" & .Cells(Rows.Count, "A").End(xlUp).Row).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlNo
- .Range("F3:I" & .Cells(Rows.Count, "I").End(xlUp).Row).Sort Key1:=.Range("I3"), Order1:=xlAscending, Header:=xlNo
- .Range("K3").Resize(.Cells(Rows.Count, 1).End(xlUp).Row - 2, 1).Formula = "=H3-C3"
- End With
-
- With Application
- .ScreenUpdating = True
- .EnableEvents = True
- .DisplayAlerts = True
- .Calculation = xlCalculationAutomatic
- End With
- MsgBox Timer - ttt & " sec spent"
- End Sub
复制代码 |
|