string - 循环浏览工作簿但排除一些工作表？

Question

这个问题已经在网上被问过好几次了，但宏中没有一个全面的程序。我在一些表格上有数据，但我需要在一些单独的“分析表格”上更具体地分析这些数据。我认为问题可能与 ThisWorkbook.Sheets("sh") 有关功能，因为它发出 subscript out of range错误。

以下是代码，请响应您为使此代码正常工作所做的任何更改!

Sub Excludesheet()
    Dim sh As Worksheet
    Const excludeSheets As String = "1-Analysis,2-Analysis"
    'Assigns a Worksheet Object to the sh variable
    For Each sh In ThisWorkbook.Worksheets
        If IsError(Application.Match(sh.Name, Split(excludeSheets, ","))) Then
        'This is for analysis worksheets
        Range("$A$1").Value = "Analysis"
        Else:
            'Data Sheets
            Columns("A:M").AutoFit
            LR = Cells(Rows.Count, "A").End(xlUp).Row
            For i = LR To 2 Step -1
                If Cells(i, "E").Text = "N/A" Then Rows(i).EntireRow.Delete
            Next i

            LastR = Cells(Rows.Count, "A").End(xlUp).Row
            Dim strFormulas(1 To 3) As Variant

            With ThisWorkbook.Sheets("sh")
                strFormulas(1) = "=(E2-$E$2)"
                strFormulas(2) = "=(G2-$G$2)"
                strFormulas(3) = "=H2+I2"

                Range("H2:J2").Formula = strFormulas
                Range("H2:J" & LastR).FillDown
            End With
        End If
    Next
End Sub

Answer 1

为了进一步澄清我的评论，请直接处理您的对象。
Check this out for various ways on how you'll do that .

现在试试你的重构代码:

Sub Excludesheet()
    Dim sh As Worksheet
    'Const excludeSheets As String = "1-Analysis,2-Analysis"
    Dim excludeSheets: excludeSheets = Array("1-Analysis", "2-Analysis")
    For Each sh In ThisWorkbook.Worksheets
        With sh 'you already have the sheet object, so work with it
            If Not IsError(Application.Match(.Name, excludeSheets, 0)) Then
                'This is for analysis worksheets
                .Range("$A$1").Value = "Analysis"
            Else
                'Data Sheets
                .Columns("A:M").AutoFit
                LR = .Cells(.Rows.Count, "A").End(xlUp).Row

                For i = LR To 2 Step -1
                    If .Cells(i, "E").Text = "N/A" Then .Rows(i).EntireRow.Delete
                Next i

                LastR = .Cells(.Rows.Count, "A").End(xlUp).Row
                Dim strFormulas(1 To 3) As Variant

                strFormulas(1) = "=(E2-$E$2)"
                strFormulas(2) = "=(G2-$G$2)"
                strFormulas(3) = "=H2+I2"

                .Range("H2:J2").Formula = strFormulas
                .Range("H2:J" & LastR).FillDown
            End If
        End With
    Next
End Sub