I get error 13 type mismatch vba when run another micro

Question

I have to macro one for changing the text in rang to uppercase and the other one for clear rang content. This the first one

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

It work fine and when I enter a value it changes it to uppercase, But when I run this macro to clear range content

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

I get

Runtime Error 13 type mismatch

And when I press debug button it marks this line

.Value = UCase(.Value)

So, How can I fix that?

You can't do that to multiple cells in one go so either add a check in your change event that target.count=1 or disable events in your clearcell procedure. — SJR

Joe Joe · Accepted Answer · 2018-10-29T17:58:15

It's happening because .Value is an array if the target range has more than one cell, and you cant call UCase on an array.

You could get round this by processing each cell one at a time:

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

though this will have a performance impact.

Another point is that you appear to be attempting to set the whole of the Target range to upper case, not only the part that overlaps with B9:B28. If you only want B9:B28 to be forced to upper case, you need something like:

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

I get error 13 type mismatch vba when run another micro

1 Answers