时间戳

首页 > 解决方案 > 跳过没有 IF 语句的工作表

excel - 跳过没有 IF 语句的工作表

问题描述

我这里有一些代码，但我希望它跳过工作表名称Aggregated、、、、。我试图添加一个语句来跳过这些，但它似乎不喜欢它。Collated ResultsTemplateEndIf

Sub FillBlanks()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range

    For Each ws In Worksheets
        Set rng2 = ws.Range("L1:AB40")

        On Error Resume Next
        Set rng1 = rng2.SpecialCells(xlBlanks)
        on error goto 0

        if not rng1 is nothing then
            Application.Iteration = True
            rng1.FormulaR1C1 = "=AVERAGE(R[-1]C,R[1]C)"
            Application.Iteration = False
            rng2.Value = rng2.Value
        end if    
    Next ws 
End Sub

标签： excelvba

解决方案

如果您将要跳过的工作表的名称添加到该行worksheetsToSkip = array("...（下面），那么下面的代码应该跳过所述工作表。

Option Explicit

Sub FillBlanks()

    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range

    Dim worksheetsToSkip As Variant
    worksheetsToSkip = Array("Aggregated", "Collated Results", "Template", "End")

    For Each ws In Worksheets
        If IsError(Application.Match(ws.Name, worksheetsToSkip, 0)) Then
             Set rng2 = ws.Range("L1:AB40")

             On Error Resume Next
             Set rng1 = rng2.SpecialCells(xlBlanks)
             On Error GoTo 0

            If Not rng1 Is Nothing Then
                 Application.Iteration = True
                 rng1.FormulaR1C1 = "=AVERAGE(R[-1]C,R[1]C)"
                 Application.Iteration = False
                 rng2.Value = rng2.Value
            End If
        End If
    Next ws
End Sub

推荐阅读