循环中存在运行时错误91

问题描述：

Sub clearSheet(WSName As String) 

    Dim ws As Worksheet 
    Set ws = Nothing 

    With ActiveWorkbook 
     Dim blWSExists As Boolean 
     blWSExists = False 
     For i = 1 To .Sheets.Count 
      If .Sheets(i).Name = WSName Then 
       blWSExists = True 
       .Sheets(i).Activate 
       .Sheets(i).Visible = xlSheetVisible 
      End If 
     Next 
     If Not blWSExists Then 
      Set ws = .Sheets.Add 
      ws.Move after:=.Sheets(.Sheets.Count) 
      ws.Name = WSName 
      ws.Visible = xlSheetVisible 
     End If 
     .Sheets(WSName).AutoFilterMode = False 
     .Sheets(WSName).Cells.Clear 
     .Sheets(WSName).UsedRange.ClearOutline 
     .Sheets(WSName).Cells.ClearFormats 
    End With 

End Sub

tr y颠倒2行的顺序，首先使它可见'.Sheets（i）.Visible = xlSheetVisible'，然后激活它'.Sheets（i）.Activate' –

nop没有工作 –

也许它没有，但无论如何，你需要切换它们。你在哪一行得到这个错误？ –

答

试一下：

Dim ws As Worksheet 
Dim blWSExists As Boolean 

blWSExists = False 

For Each ws In Worksheets 
    If ws.Name = WSName Then 
     blWSExists = True 
    End If 
Next 

If Not blWSExists Then 
    ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 
    ActiveSheet.Name = WSName 
End If 

Set ws = ActiveWorkbook.Sheets(WSName) 

ws.AutoFilterMode = False 
ws.Cells.Clear 
ws.UsedRange.ClearOutline 
ws.Cells.ClearFormats 
ws.Activate

这条线上有运行时错误1004 :(“For Each ws In Worksheets” –

@HA尝试在For循环之前放置'Set ws = Nothing'这个错误在这里没有发生。 –

答

您可以使用Cells.Delete()清除一切工作表上：

Sub clearSheet(WSName As String) 

    Dim s As Object 

    For Each s in ThisWorkbook.Sheets 
     If s.Name = WSName Then 
      s.Visible = xlSheetVisible 
      If TypeOf s Is worksheet Then s.Cells.Delete ' not all Sheets have cells, but all Worksheets do 
      Exit Sub          ' to ignore the rest of the code if the Sheet exists 
     End If 
    Next 

    ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = WSName 

End Sub

或只是删除工作表，并添加新的清除与相关联的绝对一切表：

Sub clearSheet(WSName As String) 
    On Error Resume Next 
    Sheets(WSName).Delete 
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = WSName 
End Sub

循环中存在运行时错误91

相关推荐