时间戳

首页 > 解决方案 > 循环数据验证列表

excel - 循环数据验证列表

问题描述

我想要做的是循环数据验证，当我找到匹配项时，从数据验证中选择选项。下面是我的代码：

Option Explicit

Sub Insert()

    Dim LastRow As Long, i As Long
    Dim str As String
    Dim rng As Range, Opt As Range

    With ThisWorkbook.Worksheets("Sheet1")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 1 To LastRow

            str = .Range("A" & i).Value

            Set rng = Evaluate(.Range("B" & i).Validation.Formula1)

            For Each Opt In rng

                If Opt.Value = str Then
                    Opt.Select
                End If

            Next

        Next i

    End With

End Sub

我得到一个：

运行时错误“424”

在线的：Set rng = Evaluate(.Range("B" & i).Validation.Formula1)

键入：?.Range("B" & i).Validation.Formula1在即时窗口中，从数据验证列表中获取所有值。

任何帮助将不胜感激！

回答

我设法做的是以下内容：

Option Explicit

Sub Insert()

    Dim LastRow As Long, i As Long, y As Long
    Dim str As String
    Dim arr As Variant, element As Variant

    With ThisWorkbook.Worksheets("Sheet1")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 1 To LastRow

            str = .Range("A" & i).Value

            arr = Split(.Range("B" & i).Validation.Formula1, ",")

            For y = LBound(arr, 1) To UBound(arr, 1)

                If InStr(1, arr(y), str) > 0 Then
                    .Range("B" & i) = arr(y)
                    Exit For
                Else
                    .Range("B" & i).ClearContents
                End If

            Next y

        Next i

    End With

End Sub

标签： excelvba

解决方案

您的代码仅适用于具有数据验证集且Formula1包含范围的单元格。数据验证有一个属性Type可以告诉您正在使用哪种验证。类型列表可在https://docs.microsoft.com/en-us/office/vba/api/excel.xldvtype找到

对于您的情况（值列表），这是类型 3。因此，您应该在分配之前检查验证是否具有类型 3。不幸的是，如果没有为单元格设置验证并且您检查验证类型，您将收到运行时错误 (1004)。

这可以用类似的代码来处理

On Error Resume Next
Dim hasValidation  As Boolean
hasValidation = (rng.Validation.Type = 3)
On Error GoTo 0
If hasValidation Then
    ....

我承认这并不比写好多少

 On Error Resume Next
 set rng = Nothing
 set rng = Evaluate(.Range("B" & i).Validation.Formula1)

 On Error Goto 0
 if not rng is Nothing then
    ....

另一种方法是仅循环那些具有数据验证的单元格

dim cell as range
For Each cell In .Range("B:B").EntireRow.SpecialCells(xlCellTypeAllValidation)
    if cell.Validation.type = 3 Then
        set rng = Evaluate(cell.Validation.Formula1)
        ....

当包含 a时，技巧Evaluate应该起作用，无需修改字符串（删除或类似的东西）。Formula1Range=

最后一点：在您的原始代码中，您只需Select对找到的值执行 a 操作，但您继续执行检查所有具有验证的单元格的循环。如果您有多个带有数据验证的单元格，您将必须下定决心应该发生什么。

推荐阅读