我是VBA的新手,我正在尝试创建一个程序,通过某些单元格范围的列循环,并为这些单元格创建数据验证。我选择了选项"文本长度"——我希望最小值为1,最大值为单元格引用(但是,单元格引用也应该随着各自的列而变化)。以下是我到目前为止所做的——使用宏记录和稍微编辑代码。
我设置了一个ranges变量(对于第一个循环,它应该看起来像R16C2:R500C2),以便它在一个循环列中选择给定的范围。"Formula2"应随给定列的变化而变化,但应保留在第14行。
任何帮助都是感激的!
Sub Validation()
'
' Validation Macro
For i = 2 To 84
Dim ranges As String
ranges = "R16C" + Str(i) + ":R500C" + Str(i)
With ThisWorkbook.Worksheets(1)
Columns(i).Select
Application.Goto Reference:=Range(ranges).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="=$B$14"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End With
Next i
End Sub
就用
Formula2:="=B$14"
你也不需要任何循环:
Sub Validation()
With ThisWorkbook.Worksheets(1).Range("B16:CF500").Validation
.Delete
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="=B$14"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
您需要更改下面的行
Operator:=xlBetween, Formula1:="1", Formula2:="=$B$14"
Operator:=xlBetween, Formula1:="1", Formula2:="=$" & Chr(64+i) & "$14"