- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 112
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-5-15
              
|
回復 1# john2006168 - Sub Ex()
- Dim Ar()
- Set d = CreateObject("Scripting.Dictionary")
- Set dc1 = CreateObject("Scripting.Dictionary")
- With Sheet1
- For Each a In .Range(.[A2], .[A65536].End(xlUp))
- d(a.Value) = a.Offset(, 1)
- Next
- For Each a In .Range(.[D2], .[D65536].End(xlUp))
- dc1(a.Value) = a.Offset(, 1)
- Next
- End With
- Sheet2.Columns("A:E") = ""
- For Each ky In d.keys
- If d(ky) <> dc1(ky) And d(ky) <> "" Then
- ReDim Preserve Ar(s)
- Ar(s) = Array(ky, d(ky))
- s = s + 1
- End If
- Next
- If s > 0 Then Sheet2.[A1].Resize(s, 2) = Application.Transpose(Application.Transpose(Ar))
- s = 0: Erase Ar
- For Each ky In dc1.keys
- If d(ky) <> dc1(ky) And dc1(ky) <> "" Then
- ReDim Preserve Ar(s)
- Ar(s) = Array(ky, dc1(ky))
- s = s + 1
- End If
- Next
- If s > 0 Then Sheet2.[D1].Resize(s, 2) = Application.Transpose(Application.Transpose(Ar))
- End Sub
|
|
發表於 2010-5-19 18:17
| 