excel - 运行另一个 micro 时出现错误 13 type mismatch vba

Question

我必须宏
一个用于将范围中的文本更改为大写，另一个用于清除范围内容。
这是第一个

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("B9:B28")) _
  Is Nothing) Then
    With Target
        If Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
End If
End Sub

它工作正常，当我输入一个值时，它会将其更改为大写，但是当我运行此宏以清除范围内容时

Sub clearCellContentsKeepFormatting()
    Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Are you sure about this?", vbYesNo + vbQuestion, "Clear All Proudcts")

    If Answer = vbYes Then
        Range("B9", "B28").ClearContents
        Range("C9", "C28").ClearContents
    Else
        Exit Sub
    End If
End Sub

我明白了

Runtime Error 13 type mismatch

当我按下调试按钮时，它会标记这一行

.Value = UCase(.Value)

那么，我该如何解决呢？

Answer 1

发生这种情况是因为 .Value 是一个数组，如果目标范围有多个单元格，并且您不能调用 UCase在一个数组上。

您可以通过一次处理每个单元格来解决这个问题:

Dim c As Range
With Target
    If Not .HasFormula Then
        Application.EnableEvents = False
        For Each c In Target
            c.Value = UCase(c.Value)
        Next c
        Application.EnableEvents = True
    End If
End With

尽管这会对性能产生影响。

另一点是您似乎试图将整个目标范围设置为大写，而不仅仅是与 B9:B28 重叠的部分。如果您只希望 B9:B28 强制为大写，则需要以下内容:

Dim rngIntersection As Range
Set rngIntersection = Application.Intersect(Target, Range("B9:B28"))

If Not (rngIntersection Is Nothing) Then
    Dim c As Range
    With rngIntersection
       If Not .HasFormula Then
            Application.EnableEvents = False
            For Each c In rngIntersection
                c.Value = UCase(c.Value)
            Next c
            Application.EnableEvents = True
        End If
    End With
End If