无法给数组赋值

Question

我试图清除工作簿中存在的工作表中的值。我有一个所有可能(有效)工作表的列表，但我不知道工作簿中当前存在哪个工作表。所以，我需要得到工作表的名称，看看它是否有效，然后清除其内容。以下是目前为止的内容:

Sub testclear()
Dim validsheets() As Variant, sheetstoclear() As Variant
Dim i as Integer, j As Integer, k As Integer, m as Integer
validsheets() = Array ("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")
For i = 1 To Worksheets.count
For j = LBound(validsheets) to UBound(validsheets)
If Worksheets(i).Name = validsheets(J) Then
sheetstoclear(k) = Worksheets(i).Name
k = k +1
End If
Next j
Next i
For m = LBound(sheetstoclear) to UBound(sheetstoclear)
Sheets(sheetstoclear(m+1)).Cells.clear
Next m

End Sub

如果我执行上面的代码，我得到以下错误-

Run-time error'9':
Subscript out of range

Answer 1

迭代sheets集合并直接清除表，而不首先创建sheetstoclear数组。

Option Explicit
Sub testclear()
Dim ws As Worksheet, validsheets, var
validsheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")

For Each ws In ThisWorkbook.Sheets
For Each var In validsheets
If var = ws.Name Then
ws.Cells.Clear
Exit For
End If
Next
Next
End Sub

Answer 2

请尝试下一个简单的方法:

Dim ws As Worksheet
For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"))
ws.UsedRange.Clear
Next