发现重复值时，VBA 计数并打印每个单元格地址

Question

我正在尝试计算找到重复值的单元格的数量
然后在消息框中打印它们。
目前我的代码成功找到了值，但只打印具有第二个重复值的单元格，而不是所有具有相同值的单元格。它必须是这样的:

id "wrong id"被多次使用 ("ids") 。
*所有单元格的ID，用逗号分隔。在这个例子中“A6，A7”。这是我的代码:

Sub sbFindDuplicatesInColumn()

Dim cell As Range
Dim mess As String
Dim rngcheck As Range
Dim rng1 As Range
Dim C As Range
Dim objDic
Dim strMsg As String

Set objDic = CreateObject("scripting.dictionary")
Set rng1 = Range([a1], Cells(Rows.count, "A").End(xlUp))
For Each C In rng1

    If Len(C.Value) > 0 Then
        If Not objDic.exists(C.Value) Then
            objDic.Add C.Value, 1
        Else
            strMsg = strMsg & "The id" & C.Value & " is used multiple times " & C.Address(0, 0) & vbNewLine
        End If
    End If
Next
    If Len(strMsg) > 0 Then MsgBox strMsg
End Sub

Answer 1

所以，说你的 list 是

1
2
3
1
5
2

您的预期输出是:

The id '1' is used multiple times: A1, A4
The id '2' is used multiple times: A2, A6

但你的实际输出是

The id '1' is used multiple times: A4
The id '2' is used multiple times: A6

这是因为你不存储第一次遇到的索引值，只存储 那你遇到它。

尝试这样的事情:

For Each C In rng1   
    If Len(C.Value) > 0 Then
        If Not objDic.exists(C.Value) Then
            objDic.Add C.Value, CStr(C.Address)
        Else
            objDic(C.Value) = objDic(C.Value) & ", " & CStr(C.Address)
        End If
    End If
Next

Dim comma As String, strMsg As String
strMsg = ""
For Each i In objDic.Keys
    pcs = Split(objDic(i), ",")
    If Ubound(pcs) > 1 Then
        strMsg = strMsg & "The id" & i & " is used multiple times "
        comma = ""
        For Each p In pcs
            strMsg = strMsg & comma & p
            comma = ", "
        Next
        strMsg = strMsg & vbNewLine
    End If
Next
If Len(strMsg) > 0 Then MsgBox strMsg