CODEWITHSUNDEEP
excelvbarange
Shaves
Shaves

Reputation: 930

Excel VBA Clearcontents method of Range class failed

The code below is from one of the first macros I wrote so I'm sure there are much better ways to do this. This code has worked for years and recently started failing and displaying the "Clearcontents method of Range Class failed" message.

I have 2 fields where the user can enter an "X". The intent of this code was to clear the other cells when a user enters an "X". Pretty simple and straightforward. Any suggestions?

This is happening on a machine with Windows 7 and a 32 bit operating system.

Thanks for the help........

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 19 And Target.Column = 2 And Len(Trim(Cells(19, 2))) > 0 Then
        Cells(19, 2) = UCase(Cells(19, 2))
        Cells(20, 2).ClearContents
    Else
        If Target.Row = 20 And Target.Column = 2 And Len(Trim(Cells(20, 2))) > 0 Then
            Cells(20, 2) = UCase(Cells(20, 2))
            Cells(19, 2).ClearContents
        End If
    End If

End Sub

Upvotes: 1

Views: 4346

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
        If Target.Row = 19 And Target.Column = 2 And Len(Trim(Cells(19, 2))) > 0 Then
            Cells(19, 2) = UCase(Cells(19, 2))
            Cells(20, 2).ClearContents
        Else
            If Target.Row = 20 And Target.Column = 2 And Len(Trim(Cells(20, 2))) > 0 Then
                Cells(20, 2) = UCase(Cells(20, 2))
                Cells(19, 2).ClearContents
            End If
        End If
    Application.EnableEvents = True
End Sub

Upvotes: 0

Michel Excel
Michel Excel

Reputation: 25

Try to change Cells(20, 2).ClearContents to Cells(20, 2).value = "".

Or try to qualify the object Cells with the worksheet name like this: Worksheets("putNameHere").Cells(20, 2).ClearContents.

The code has some problems, but maybe it works...

Upvotes: 1

Related Questions