vba - 为什么我在此 MyCell 范围错误中出现类型不匹配？

Question

我在这一行不断收到运行时错误:

                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:=MgrPath & "2018 Mid-Year Comp Statement - " & SM.Range("C5").Value & ".pdf", _
                                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                OpenAfterPublish:=False

子语句_Autoprint()

        Dim MCST As Workbook: Set MCST = ActiveWorkbook
        Dim User As String: User = Environ$("Username")
        Dim SavePath As String: SavePath = "M:\comp_statements\"
        Dim CS As Worksheet: Set CS = MCST.Sheets("Control Sheet")

        Dim MgrPath As String, MyCell As Range, Printed As Integer, i As Integer, SM As Worksheet
        Printed = 0


    Call Disable

For i = 2 To CS.Range("B" & CS.Rows.Count).End(xlUp).Row
    If CS.Range("A" & i) <> "" & CS.Range("B" & i) <> "" Then
        Set SM = MCST.Sheets(CStr(CS.Range("A" & i)))
        SM.Calculate
        SM.Range("P1") = Format(CS.Range("B" & i), "000000000")

            For Each MyCell In SM.Range("N2:N70")
                If MyCell = "HIDE" Then
                    MyCell.EntireRow.Hidden = True
                ElseIf MyCell <> "HIDE" Then
                    MyCell.EntireRow.Hidden = False
                End If
            Next MyCell

        If Not Application.CalculationState = xlDone Then
            DoEvents
        End If

                MgrPath = "M:\Pittsburgh\GRP4\HR_PCorpComp\2018 Midyear\Reporting\Parsley\comp_statements\" & SM.Range("K5") & "\"


                If Dir(MgrPath, vbDirectory) <> "" Then
                    MkDir MgrPath
                End If

                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                    Filename:=MgrPath & "2018 Mid-Year Comp Statement - " & SM.Range("C5").Value & ".pdf", _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                    OpenAfterPublish:=False

                    Printed = Printed + 1
                End If
            Next i

            CS.Activate

            Call Re_Enable


End Sub

我没有任何以该名称存在/打开的文件，我不知道是什么阻止了它的保存。所有其他代码位都做了他们应该做的事情，它只是不能循环到下一个员工，因为由于那个错误，保存被抑制了。

Answer 1

尝试这个

For Each mycell In SM.Range("N2:N70")
    If IsError(mycell) Then
        Debug.Print mycell.Address
    Else
        mycell.EntireRow.Hidden = (mycell = "HIDE")
    End If
Next mycell

使用 IsError 处理错误或

转到上述代码指向的单元格并检查是否有任何公式错误。

如果单元格有公式错误，您通常会收到该错误。