vba把数字重复在一起

Okay so I have a variable sized array in Excel. Using vba I'd like to find if there are any repeating values, so values that are next to each other that are the same, not just in the whole column.

Excel中有一个可变大小的数组。使用vba，我想找出是否有重复的值，所以相邻的值是相同的，而不仅仅是在整个列中。

I know just in excel you could use an if statement, but how could you just spread that out to the whole sheet using for loops.

我知道在excel中你可以用if语句，但是你怎么能用for循环把它分散到整个表中。

Im extremely novice to vba. This was my first attempt, it didn't work though and I don't know how to continue.

我对vba还不熟。这是我的第一次尝试，但是没有成功，我不知道如何继续下去。

total is a value entered in by the user

total是用户输入的值

ok = True
For j = 2 To 24
    For l =1 to total
        If ActiveCell.Offset(j, l).Select = ActiveCell.Offset(j + 5, l)
        And ActiveCell.Offset(j + 4, l) And ActiveCell.Offset(j + 3, l) 
        And ActiveCell.Offset(j + 2, l) And ActiveCell.Offset(j + 1, l) Then
            ok = False
        End if
    Next
Next

2 个解决方案

#1

If you just want to flag rows that immediately repeat, just check the value in the row above to see if it's the same. If so, flag the column. For example:

如果只想标记立即重复的行，只需检查上面一行中的值，看看是否相同。如果是，标记列。例如:

With Range("B2:B24")
    .Formula = "=IF(A2=A1,""X"","""")"
    .Value = .Value
End With

This is how it would appear for the following dataset:

以下数据集是这样显示的:

╔════════╦═══════╗
║ Col A  ║ Col B ║
╠════════╬═══════╣
║     17 ║       ║
║     44 ║       ║
║     44 ║ X     ║
║     44 ║ X     ║
║     15 ║       ║
║     93 ║       ║
║     93 ║ X     ║
║     16 ║       ║
╚════════╩═══════╝

#2

If you're just wanting to check if there are duplicates next to one another in the column, the below will do what you're after.

如果您只是想检查列中是否有彼此相邻的副本，下面的代码将执行您所需要的操作。

Sub arrayDuplicateCheck()
    Dim arrValues() As Variant
    Dim lrow As Long, i As Long, ok As Boolean

    ok = False
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    arrValues = Range(Cells(1, 1), Cells(lrow, 1))

    For i = 1 To UBound(arrValues, 1) - 1
        If arrValues(i, 1) = arrValues(i + 1, 1) Then
            ok = True
            Exit For
        End If
    Next i

    MsgBox (ok)
End Sub

This is currently checking values within column A, you will have to adjust the range which is assigned to the array if you wish to check a different column. The do this, update the arrValues = Range(Cells(1, 1), Cells(lrow, 1)) to the column number you want to evaluate. If you're changing the array column, you will need to update the lrow variable to the same column number.

当前正在检查列A中的值，如果您希望检查另一列，则必须调整分配给数组的范围。执行此操作时，将arrValues = Range(单元格(1,1)、单元格(lrow, 1))更新到要计算的列号。如果要更改数组列，则需要将lrow变量更新为相同的列号。

For example, arrValues = Range(Cells(1, 4), Cells(lrow, 4)) would now create the array from Column D.

例如，arrValues = Range(单元格(1,4)、单元格(lrow, 4))现在将从列D创建数组。

Edit: If you want to check if all 5 rows below match, then you can just change the For and If statement to this:

编辑:如果你想检查下面的5行是否匹配，你可以将For和If语句改为:

For i = 1 To UBound(arrValues, 1) - 5
    If arrValues(i, 1) = arrValues(i + 1, 1) _
    And arrValues(i, 1) = arrValues(i + 2, 1) _
    And arrValues(i, 1) = arrValues(i + 3, 1) _
    And arrValues(i, 1) = arrValues(i + 4, 1) _
    And arrValues(i, 1) = arrValues(i + 5, 1) Then
        ok = True
        Exit For
    End If
Next i

#1