运行时错误'1004':重置单元格时出错 [英] Run-time error '1004': Error when resetting cells

问题描述

我正在编写代码以重置工作表中的单元格.很好，然后突然停止工作了.这是重置代码.

I am writing a code to reset cells on my sheet. It was fine then all of a sudden it stopped working. This is the reset code.

Private Sub Reset_Click()

Range(Cells(20, 2), Cells(41, 13)).Interior.Color = RGB(217, 217, 217)
Range(Cells(20, 2), Cells(41, 13)).Font.Color = RGB(255, 255, 255)

Range(Cells(3, 2), Cells(11, 13)).Interior.Color = RGB(255, 255, 255)
Range(Cells(13, 2), Cells(18, 13)).Interior.Color = RGB(255, 255, 255)

End Sub

预先感谢

推荐答案

以下是调整后的代码(应该可以使用):

Here is the adjusted code (which should work):

Option Explicit

Private Sub Reset_Click()

Dim ws As Worksheet

' Change in the following line the name of the sheet (if necessary)
Set ws = ThisWorkbook.Worksheets("Sheet1")

ws.Range(ws.Cells(20, 2), ws.Cells(41, 13)).Interior.Color = RGB(217, 217, 217)
ws.Range(ws.Cells(20, 2), ws.Cells(41, 13)).Font.Color = RGB(255, 255, 255)

ws.Range(ws.Cells(3, 2), ws.Cells(11, 13)).Interior.Color = RGB(255, 255, 255)
ws.Range(ws.Cells(13, 2), ws.Cells(18, 13)).Interior.Color = RGB(255, 255, 255)

End Sub

基本上，您应该使用适用的工作簿和工作表对所有 Range 和 Cells 进行限定.否则，您将其留给VBA来猜测要使用的Excel文件以及应更改文件中的哪张纸.

Basically, you should qualify all of your Range and Cells with the applicable workbook and worksheet. Otherwise, you leave it to VBA to guess which Excel file to use and which sheet in the file should be altered.

_{由于是上面的评论，这是社区的工作，因此我将此答案作为社区Wiki的答案.}

这篇关于运行时错误'1004':重置单元格时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！