- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
回復 4# sping
純參考
用Find方法
- Sub 同工作表不同欄位之比對()
- Dim Rng(1 To 2) As Range, F As Range, d1 As Object, d2 As Object, A As Range
- Set d1 = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- d1("相同Name") = ""
- d2("不相同Name") = ""
- With Sheets("同工作表之比對")
- Set Rng(1) = .Range("A3", .Range("A" & Rows.Count).End(xlUp))
- Set Rng(2) = .Range("B3", .Range("B" & Rows.Count).End(xlUp))
- For Each A In .Range(Rng(1).Address & "," & Rng(2).Address)
- If A <> "" Then
- Set F = Rng(IIf(A.Column = Rng(1).Column, 2, 1)).Find(A.Text, lookat:=xlWhole)
- If Not F Is Nothing Then
- d1(A.Text) = ""
- Else
- d2(A.Text) = ""
- End If
- End If
- Next
- End With
- With Sheets("同工作表之比對")
- .Range("c3:d65536") = ""
- .[c2].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
- .[d2].Resize(d2.Count, 1) = Application.Transpose(d2.keys)
- End With
- End Sub
|
|
發表於 2010-6-23 16:09
| 
