excel - 如何在 Excel 2016 中使用 VBA 查找重复项并分别列出它们

Question

答案“如何在 Excel 中使用 VBA 查找重复项并分别列出它们？” 正是我正在寻找的。我需要帮助编辑语言以适合我的电子表格。任何帮助是极大的赞赏。我不是程序员，只是欣赏 VBA 强大功能的最终用户。谢谢你。

我尝试编辑语法，但无法在我的电子表格中使用。

Sub find_dups()

    ' Create and set variable for referencing workbook
    Dim wb As Workbook
    Set wb = ThisWorkbook

    ' Create and set variable for referencing worksheet
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")

    ' Find current last rows
    ' For this example, the data is in column A and the duplicates are in column C
    Dim lngLastRowME As Long
    lngLastRowME = ws.Range("A29-7834-9-0003").End(xlUp).Row
    Dim lngLastRowDups As Long
    lngLastRowDups = ws.Range("C29-7834-9-0003").End(xlUp).Row

    ' Create and set a variable for referencing data range
    Dim rngME As Range
    Set rngME = ws.Range("a4:a" & lngLastRowME)

    Dim lngRowCount As Long
    lngRowCount = 0

    Dim clME As Variant
    Dim lngCount As Long

    Dim lngRowIndexME As Long
    Dim lngRowIndexDups As Long
    lngRowIndexDups = lngLastRowDups + 1

    ' Variable to store those values we've already checked
    Dim strAlreadySearched As String


    For Each clME In rngME.Cells

        ' Reset variables
        lngCount = 0


        ' See if we've already searched this value
        If InStr(1, strAlreadySearched, "|" & clME.Value & "|") = 0 Then

            ' We haven't, so proceed to compare to each row
            For lngRowIndexME = 1 To lngLastRowME

                ' If we have a match, count it
                If rngME.Cells(lngRowIndexME, 1).Value = clME.Value Then
                    lngCount = lngCount + 1
                End If

            Next lngRowIndexME

            ' If more than 1 instance
            If lngCount > 1 Then
                ' Dup's were found, fill in values under duplicates
                ws.Cells(lngRowIndexDups, 3).Value = clME.Value
                ws.Cells(lngRowIndexDups, 4).Value = lngCount

                ' Drop down a row
                lngRowIndexDups = lngRowIndexDups + 1

                ' Capture this value so we don't search it again
                strAlreadySearched = strAlreadySearched & "|" & clME.Value & "|"


            End If
        End If

    Next clME



End Sub

运行时错误 400

屏幕截图和工作簿

标签： excelvba