时间戳

首页 > 解决方案 > 循环遍历具有多个 for 循环的工作表，并且内部的 If 函数不起作用

vba - 循环遍历具有多个 for 循环的工作表，并且内部的 If 函数不起作用

问题描述

该代码有效，但仅适用于当前工作表。我的目标是通过循环将功能代码应用于所有工作表。这段代码似乎没有循环。

Sub sbDelete_Rows_IF_Cell_Cntains_String_Text_Value()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Dim lRow As Long
    Dim iCntr As Long
    lRow = 200
    For iCntr = lRow To 1 Step -1
        If Cells(iCntr, 3) = "PUT" Or Cells(iCntr, 3) = "CALL" Or Cells(iCntr, 3) = "PRN" Then
            Rows(iCntr).Delete
        End If
    Next
Next ws
End Sub

如果我将 ws 名称添加到“单元格”，则会在“如果”行出现不匹配错误。这里出了什么问题？

Sub sbDelete_Rows_IF_Cell_Cntains_String_Text_Value()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Dim lRow As Long
    Dim iCntr As Long
    lRow = 200
    For iCntr = lRow To 1 Step -1
        If ws.Cells(iCntr, 3) = "PUT" Or ws.Cells(iCntr, 3) = "CALL" Or ws.Cells(iCntr, 3) = "PRN" Then
            Rows(iCntr).Delete
        End If
    Next
Next ws
End Sub

标签： vbaexcel

解决方案

始终声明父工作表（以及父工作簿，每当使用更多工作簿时）是一个很好的做法：

Sub sbDelete_Rows_IF_Cell_Cntains_String_Text_Value()

    On Error GoTo sbDelete_Rows_IF_Cell_Cntains_String_Text_Value_Error

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            Dim lRow As Long
            Dim iCntr As Long
            lRow = 200
            For iCntr = lRow To 1 Step -1
                If .Cells(iCntr, 3) = "PUT" Or .Cells(iCntr, 3) = "CALL" Or .Cells(iCntr, 3) = "PRN" Then
                    .Rows(iCntr).Delete
                End If
            Next
        End With
    Next ws

   On Error GoTo 0
   Exit Sub

sbDelete_Rows_IF_Cell_Cntains_String_Text_Value_Error:

    MsgBox "Error " & Err.Number & " on cell " & Cells(iCntr, 3).Address

End Sub

推荐阅读