使用VBA遍历数据验证列表中的每一项

excelperfect

标签：VBA，数据验证

想要遍历数据验证列表中的每一项，如何编写VBA代码呢？如果数据验证列表中的项值来源于单元格区域或者命名区域，则很简单，遍历该区域即可。然而，有些数据验证列表是直接使用逗号分隔的项添加的，这就需要使用不同的方法。

数据验证设置基于下面的4种方法：

1.单元格引用，如下图1所示。

图1

2.命名区域，如下图2所示。

图2

3.溢出单元格区域，如下图3所示。

图3

4.逗号分隔的列表，如下图4所示。

图4

下面的代码适用于上述4种情形，遍历数据验证列表中的每项：

Option Explicit
Sub LoopThroughDataValidationList() Dim rng As Range Dim varDataValidation As Variant Dim i As Integer Dim iRows As Integer '设置包含数据验证列表的单元格 Set rng = Sheets('Sheet1').Range('C1') '如果数据验证列表不是单元格区域则忽略错误 On Error Resume Next '从数据验证公式创建数组,而不是从单元格区域创建多维数组 iRows = Range(Replace(rng.Validation.Formula1, '=', '')).Rows.Count ReDim varDataValidation(1 To iRows)  For i = 1 To iRows   varDataValidation(i) = Range(Replace(rng.Validation.Formula1, '=', '')).Cells(i, 1) Next i '如果不是单元格区域,则尝试拆分字符串 If Err.Number <> 0 Then Err.Clear   varDataValidation = Split(rng.Validation.Formula1, ',') End If '如果发生其它错误则退出 If Err.Number <> 0 Then Exit Sub '恢复错误检查 On Error GoTo 0 '遍历数据验证数组中所有值 For i = LBound(varDataValidation) To UBound(varDataValidation) '修改数据有效性单元格中的值 rng.Value = varDataValidation(i) '强制工作表重新计算 Application.Calculate '在此插入为操作每个项的代码 Next iEnd Sub

你可以根据实际情况，修改代码中数据验证所在的单元格，还可以添加代码来处理数据验证中的每个项值。