时间戳

首页 > 解决方案 > 尝试检查非连续行中是否存在（重复）单元格值，其中某些列可能被隐藏

excel - 尝试检查非连续行中是否存在（重复）单元格值，其中某些列可能被隐藏

问题描述

我正在处理工作表并第一次使用 vba，我喜欢它。但是在过去的几天里一直被困在一件事上，毕竟阅读和搜索无法弄清楚如何做这部分，这是我的场景：

锁定工作表和工作簿，用户只能在单元格 C8:G8 和 I8:X8 中编辑/输入值（数字），H 列始终为空白且没有值。

如果需要使用一定数量的列，用户可以隐藏 C8:G8 和 I8:X8 中的列。

试图设置一个宏来识别一个值是否在整个范围 C8:X8 内输入了多次（不包括 H，它是空的，如果隐藏了任何其他列）

我从 countif 开始，只有在所有列都可见时才能给出完美的结果：

Sub dup()

 Application.EnableEvents = False

  Dim x As Variant 'tried with range
  Dim n As Variant 'tried with range
  Dim rng1 As Range 'tried with variant

  Set rng1 = Range("C8:X8")

  For Each x In rng1.SpecialCells(xlCellTypeVisible)

        If Application.WorksheetFunction.CountIf(rng1, x) > 1 Then

           x.Offset(4) = "3" 'used for conditional formatting

       Else

           x.Offset(4) = "10" 'used for conditional formatting
      End If

   Next

   Application.EnableEvents = True

   End Sub

当某些列被隐藏时仍然有效，但它确实检查隐藏列，这不是我想要的（我希望它跳过隐藏列）一些搜索和阅读发现 countif 无法获取单元格属性（如果可见或隐藏）。尝试了两个选项 application.countif 和 application.worksheetfunction.countif

所以尝试了 application.match 但没有运气

    For Each x In rng1

If Not IsEmpty(x) Then

    n = Application.match(x.Value, rng1.Value, 0)
    If Not IsError(n) Then

    x.Offset(4) = "3"

    Else

    x.Offset(4) = "10"
    End If
End If

Next

尝试了 application.hlookup 并无法获得所需的结果：

For Each x In rng1

If Not IsEmpty(x) Then

    n = Application.HLookup(x.Value, rng1.Value, 1, False)
    If Not IsError(n) Then

    x.Offset(4) = "3"

    Else

    x.Offset(4) = "10"
    End If
End If

Next

它将匹配单元格本身，并且只查看范围 C8:G8 的第一部分。

只是为了解释隐藏列的情况，用户可以在第一个范围内隐藏/显示 1、2、3、4 和 5 列（如果用户选择 2，则只有 C8:D8 列可见）同样适用于范围 I8： X8，如果用户选择 5，则只有 I8:M8 可见）因此会有隐藏列位于可见列之间的情况。

找到一些关于如何仅将 SumProduct(subtotal,...) 用作公式并且无法将其转换为 VBA 的答案。

任何建议和建议将不胜感激。

标签： excelvba

解决方案

请尝试此解决方案。

Sub Dup()

    Const Sep As String = "|"       ' select a character that
                                    ' doesn't occur in Rng

    Dim Rng As Range
    Dim Arr As Variant
    Dim SearchString As String
    Dim n As Integer
    Dim i As Integer

    ' needed only if you have event procedures in your project:-
    Application.EnableEvents = False

    Set Rng = Range("C8:X8")
    Arr = Rng.Value
    SearchString = Sep
    For i = 1 To UBound(Arr, 2)
        If Not Columns(Rng.Cells(i).Column).Hidden Then
            SearchString = SearchString & Arr(1, i) & Sep
        End If
    Next i

    For i = 1 To UBound(Arr, 2)
        ' skip blanks, incl. column H, & hidden cells
        If (Not Columns(Rng.Cells(i).Column).Hidden) And (Len(Arr(1, i)) > 0) Then
            n = InStr(SearchString, Sep & Arr(1, i) & Sep)
            n = InStr(n + 1, SearchString, Sep & Arr(1, i) & Sep)
            With Rng.Cells(i)
                If .Column <> 8 Then    ' skip column H
                    .Offset(4).Value = IIf(n > 0, 3, 10)
                    ' Note that "3" is a string (text) whereas 3 is a number
                    ' It's unusual to enter a number as text because it's use
                    ' for calculations is greatly impaired.
                    ' However, you may modify the above line to write strings
                    ' instead of numbers.
                End If
            End With
        End If
    Next i

    Application.EnableEvents = True
End Sub

sub 将 Range 中的所有非隐藏值分配给一个数组，然后将它们读入一个字符串 (SearchString)，其中它们由一个可以重新定义的特殊字符分隔。此字符串中的所有值至少存在一次。第二个循环查找必须在特殊字符之后和之前都存在的现有值，因为“a”会在“ab”、“a|”中找到 在“巴|” 但是“|a|” 是明确的。然后从找到第一个匹配项的位置开始进行第二次搜索 (Instr)，确定是否存在重复项。然后该Iif函数将值设置在检查单元格下方 4 行的单元格中。请注意，由于数组的创建方式，数组索引与区域中的单元格编号相同。

由于该Instr函数将“找到”位置 1 中的空字符串并默认将其视为重复项，因此不处理空字符串，不为 CF 设置任何数字。因此应省略 H 列。但是，如果 H 列应该有任何值，CF 编号仍然不会被写入。

由于 sub 由事件过程调用，应用程序的 EnableEvents 属性应在该过程中设置，而不是在 sub 中。这是为了使代码更清晰，并且与功能无关，除非发泄过程也调用其他过程。

推荐阅读