✔ 最佳答案
如找到 A1, B4, C7 = 213 (共3個相同); A7, B1, C10, D2 = Yahoo(共4個相同); A5, E5=香港(共2個相同), 其他尚有 100組都有相同, 那答案如何顯示?及顯示在那裡? 請詳示!!!
2008-04-09 16:24:22 補充:
假設計算區是在Sheet1 B3(B3是固定位置)開始, 向下, 向右找尋
結果在 Sheet2 顯示。
Sub find_same()
Set x1 = Sheets("Sheet2").[A1]
x1.Offset(1, 0).Resize(1000, 256).ClearContents
r = 0
Set rg1 = Sheets("Sheet1").UsedRange
Set rg2 = Range(Sheets("Sheet1").[B3], Sheets("Sheet1").Cells(rg1.Rows.Count, rg1.Columns.Count))
For Each rgx In rg2
y1 = Application.CountIf(rg2, rgx)
y2 = Application.CountIf(x1.Resize(r + 1, 1), rgx)
If y1 > 1 And y2 = 0 And rgx <> "" Then
x1.Offset(r + 1, 0) = rgx
r = r + 1
c = 2
For Each rgy In rg2
If rgy = rgx Then
x1.Offset(r, c) = Replace(rgy.Address, "$", "")
c = c + 1
End If
Next
x1.Offset(r, 1) = c - 2
End If
Next
Beep
End Sub
如需要Excel文件, 請留e-mail