VBA批量设置单元格数据有效性

在工作中发现有员工在需要输入数量的单元格中输入16+16，导致错误结果，所以得给单元格加上数据验证，20张表，得上vba脚本。

Sub AddValidation()
Dim targetWs As Worksheet
Dim sheetIndex As Integer
Dim wsName As String

For sheetIndex = 10 To 30
  wsName = "4月" & sheetIndex & "日"
  On Error Resume Next
  Set targetWs = Worksheets(wsName)
  On Error GoTo 0
  
  If Not targetWs Is Nothing Then
    With targetWs.Range("b2:e51").Validation
    .Delete
    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlGreater, Formula1:="0"
    .IgnoreBlank = True
    .ShowInput = False
    .ShowError = True
    .ErrorMessage = "必须输入大于0的整数"
    End With
    
    With targetWs.Range("h2:h51").Validation
    .Delete
    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlGreater, Formula1:="0"
    .IgnoreBlank = True
    .ShowInput = False
    .ShowError = True
    .ErrorMessage = "必须输入大于0的整数"
    End With
  End If
Next sheetIndex
MsgBox "运行结束"

End Sub

代码说明：

1. 先执行.Delete清除原有验证规则，避免多个规则叠加
2. xlValidateWholeNumber 表示验证整数类型
3. xlGreater 配合 Formula1:="0" 实现大于0的验证
4. IgnoreBlank = True 允许单元格为空
5. ErrorMessage 自定义错误提示信息

很好的解决了这个问题。