Error 13: for cell.ClearContents

[Excel_VBA_YT]

Hi folks,

I am getting Error 13 while running the below code. If the contents of cell is New years day, I want to clear the cells contents.

The bold portion of the code is where the problem seems to be. any suggestions / advise would be great.
---------------------------------------------------------------
Sub Clear_contents()

Dim cell As Range
Dim myrange As Range
Set myrange = Worksheets("Step 1-3 Working Sheet").Range("M13:BA23")
myrange.UnMerge

For Each cell In myrange
If cell.Value = "New Years Day" Then
cell.ClearContents
End If

Next cell

End Sub
------------------------------------------------------------------

 

[Michael M]

Works fine for me.
IS NYD aan actual text or a formula result

 

[Excel_VBA_YT]

sorry, does not work that results in
"compile error: Each control variable must be variant or Object"

 

[Michael M]

Yeah, sorry Dim as Variant

 

[Trebor76]

Hard to say but ensure there are no cells in error (like #REF! or #DIV/0!) in the M13:BA23 range.

 

[Michael M]

@Trebor76
Hence my question about formula results.
Hope your keeping safe up there Robert....Are you affected ??

 

[Trebor76]

Hi Michael,
As good as can be expected thanks mate. I'm not in a lock down area so that's something.
What about you?

 

[Michael M]

Nah...we're all good down on the Murray river!

 

[mlcalves]

Hi,

this worked for me:

Sub Clear_contents()

Dim cell As Range
Dim myrange As Range
Set myrange = Sheet1.Range("A1:A23")
myrange.UnMerge

For Each cell In myrange
If cell.Value = "New Years Day" Then
cell.ClearContents
End If

Next cell

End Sub

I only changed my range

 

[RoryA]

I am getting Error 13 while running the below code

A type mismatch error on that line would suggest that the cell contains an error value. What do you want to do in that case? Clear it or ignore it?