ClearContents not working

[SGBCleve]

I am trying to run a Macro in Sheet1 which worked perfectly before. For reasons unknown to me, it is not working tonight and I get an error at the line ClearContents, RunTime Error 1004, ClearContents Method of RangeClass failed. As an aside, I am also having problems getting my computer to enter quotation marks while in the VBA editor. I have to put a space or a second character for the quote mark to show up and "I comes out as an umlaut over the I. It doesn't happen in other programs. I am stumped.


Dim rrow As Integer
Dim j As Integer
Dim k As Integer
Dim z As Integer
Dim holding(50)
Dim Sum As Integer
Dim i As Integer
Dim wrkRng As Range
Dim MaxRng As Range
Dim MinRng As Range
Dim n As Integer
Dim y As Integer






Worksheets("Sheet1").Select
Set wrkRng = Worksheets("Sheet1").Range("A1:B10")
Set MaxRng = Worksheets("Sheet1").Range("A20:A29")
Set MinRng = Sheets("Sheet1").Range("B20:B29")

Sheets("Sheet1").Activate
wrkRng.ClearContents


MaxRng.ClearContents
MinRng.ClearContents

If I put apostrophe in front of the the three range.ClearContents statements, the code runs to the next ClearContents statement and I get the same error.

Sheets(i).Select
With Sheets(i)
.Range("G3:I11").ClearContents

.Range("J3:L11").ClearContents

.Range("G2:G11").Value = Sheets("Sheet1").Range("A20:A29").Value
.Range("J2:J11").Value = Sheets("Sheet1").Range("B20:B29").Value

 

[SGBCleve]

In addition, a black bordered box is showing up, except it is not always in line with a cell, and cells I select are not highlighted.

 

[Kenneth Hobson]

Maybe the sheet is protected? You can use UserInterfaceOnly:=True for Sheet1.Protect option so that code can make changes. I normally do that for all worksheets in Thisworkbook's Open event.

Select, Activate, Selection, and such are seldom needed.

 

[My Aswer Is This]

You need a End With statement like this:

With Sheets(i)
 .Range("G3:I11").ClearContents

 .Range("J3:L11").ClearContents

 .Range("G2:G11").Value = Sheets("Sheet1").Range("A20:A29").Value
 .Range("J2:J11").Value = Sheets("Sheet1").Range("B20:B29").Value
End With

 

[SGBCleve]

It seems to have been a problem with my computer. I closed Excel and restarted it, and now it's working. Not sure why. The sheet was not protected, and the End With statement was further down, but thanks for the help