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

fanjy

发布于 2023-08-29 21:03:58

3690

发布于 2023-08-29 21:03:58

文章被收录于专栏：完美Excel完美Excel

标签：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 i
End Sub

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

本文参与?腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2023-05-03，如有侵权请联系?cloudcommunity@tencent.com 删除

vba