excel - 下标超出范围 IF ISERROR 问题

Question

这让我发疯。我一直在寻找这个问题的答案，但找不到完全匹配的。该行:

If IsError(Sheets(this_year)) Then GoTo Line99

旨在确定工作表 6th April YYYY存在。如果我使用 2019(确实存在)，则以下代码运行良好。但是，如果我使用 2020(不存在)给我

Error code 9, Subscript out of range.

    Dim this_year As String
    this_year = "6th April 2020"
    Windows("Retirement Planning - Copy.xlsx").Activate
    If IsError(Sheets(this_year)) Then GoTo Line99
    Windows("Savings Details - Copy.xlsm").Activate
    MsgBox ("Congratulation")
    GoTo Line100
    Line99:
    MsgBox ("This year does not exist")
    Line100:
End Sub

我认为“If IsError”应该“捕获”错误并做某事，但我显然做错了“令人眼花缭乱”的事情!

Answer 1

我就是这样做的。

Dim thisYear As String
Dim currSheet as Worksheet
Dim found as Boolean

thisYear = "6th April 2020"

Windows("Retirement Planning - Copy.xlsx").Activate

For Each currSheet in ActiveWorkbook.Worksheets
    If currSheet.Name = thisYear Then
         found = True
         Exit For
    End If
Next

If found Then
    MsgBox ("Congratulations! " & currSheet.Name & " Found!")
Else
    MsgBox ("The sheet named " & thisYear & " does not exist")
End If