数据验证中的 VBA 错误 - 运行时错误"1004"：应用程序定义或对象定义错误

Question

这段代码的目的是格式化一系列单元格，然后将数据验证列表编程到其中一个单元格中。但是，当我尝试这段代码时，它在到达具有Validation的代码行时继续抛出上面列出的错误。有人能帮我解决这个问题吗?也许可以解释一下为什么会触发这个错误。

Sub Test()
Dim defaultRow As Range
Dim currentCellNum As Integer
Set defaultRow = ActiveSheet.Range("R1:X1")
currentCellNum = 1
With ThisWorkbook.Worksheets("Sheet1")
.Activate
.Range("A" & currentCellNum).Select
.Range("A" & currentCellNum) = defaultRow
.Range("A" & currentCellNum).Interior.ColorIndex = 36
.Range("B" & currentCellNum).Interior.ColorIndex = 0
.Range("C" & currentCellNum).Interior.ColorIndex = 36
.Range("D" & currentCellNum).Interior.ColorIndex = 0
.Range("E" & currentCellNum).Interior.ColorIndex = 36
.Range("F" & currentCellNum).Interior.ColorIndex = 36
.Range("G" & currentCellNum).Interior.ColorIndex = 36

.Range("A" & currentCellNum).Select
.Range("A" & currentCellNum).Validation.Delete
.Range("A" & currentCellNum).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertsStop, Formula1:="A,B,C,D"
End With
End Sub

Answer 1

Sub Test()
Dim defaultRow As Range
Dim currentCellNum As Integer
Set defaultRow = ActiveSheet.Range("R1:X1")
currentCellNum = 1
With ThisWorkbook.Worksheets("Sheet2")
.Activate
.Range("A" & currentCellNum).Select
.Range("A" & currentCellNum) = defaultRow
.Range("A" & currentCellNum).Interior.ColorIndex = 36
.Range("B" & currentCellNum).Interior.ColorIndex = 0
.Range("C" & currentCellNum).Interior.ColorIndex = 36
.Range("D" & currentCellNum).Interior.ColorIndex = 0
.Range("E" & currentCellNum).Interior.ColorIndex = 36
.Range("F" & currentCellNum).Interior.ColorIndex = 36
.Range("G" & currentCellNum).Interior.ColorIndex = 36

With .Range("A" & currentCellNum).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="A,B,C,D"  '"=$E$5:$E$15"
End With

End With
End Sub