Unable to assign value to an array-CodePudding

I'm trying to clear values in the sheets that are present in a workbook. I have a list of all possible (valid) sheets, but I won't know which sheet is currently present in the workbook. So, I need to get the worksheets' name, see if it's valid and then clear its contents. Here's what I have so far:

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

If I execute the above code, I get the following error -

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

CodePudding user response：

Iterate the sheets collection and clear the sheet directly without creating a sheetstoclear array first.

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

CodePudding user response：

Please, try the next simple way:

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