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

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