如何在清除验证后分配值到excel VBA中的单元格

Question

所以我的vba函数应该根据布尔输入为单元格分配不同的值。正确的部分工作得很好，但是我被错误的陈述卡住了。True部分为单元格分配了一个下拉列表，False部分应该将其擦除，并将其值设置为文本"N/a"。一旦我试图改变单元格的值(或任何单元格实际上，与范围("…")。值命令，则代码出现错误。

有其他的方法吗?提前感谢!

Function sema_list(rng As Range, sema As Boolean)

If sema = True Then
    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=arrangement"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If
If sema = False Then
    rng.Validation.Delete
    rng.Value = "some text"
End If
sema_list = ""
End Function

Answer 1

函数只能在调用它的单元格中放置文本，因此您必须使用正常的子单元(可能带有参数)来完成此操作。例如

Sub x()
sema_list Range("A1:A2"), False
End Sub
Sub sema_list(rng As Range, sema As Boolean)
If sema = True Then
    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=arrangement"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If
If sema = False Then
    rng.Validation.Delete
    rng.Value = "some text"
End If
End Sub

Answer 2

我猜你必须在分配任何值之前传递一些范围。如:

Range("A1:A10") = "some text"

或者像这样:

Dim example As Range
Set example = Range("A1:A10")
example.Value = 8