excel - 有没有办法跳过错误 "filename is not found"并移动到下一个文件

Question

有没有办法跳过错误“找不到文件名”并移动到下一个文件？

Sub CopyDataAndMoveDown()

Application.ScreenUpdating = False

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
Dim rngToCopy As Range, rngToPaste As Range
Dim x As Long
Dim breakdown1
Dim breakdown As Worksheet: Set breakdown = wb.ActiveSheet

For x = 4 To 504 Step 6

    With wb.Sheets("Sheet1")
        breakdown1 = breakdown.Cells(9, x - 2)
    End With

    If IsEmpty(breakdown1) Then
        Call MoveBelow
    Else

        With wb.Sheets("Sheet1")
            Set rngToCopy = .Range(.Cells(4, x - 2), .Cells(24, x + 3))
            Debug.Print rngToCopy.Address
        End With

        With wb.Sheets("Sheet2")
            Set rngToPaste = .Range(.Cells(4, x - 2), .Cells(rngToCopy.Rows.Count + 3, x + 3))
            Debug.Print rngToPaste.Address
        End With

        rngToPaste = rngToCopy.Value
    End If

Next x

Application.ScreenUpdating = True
MsgBox "Valmis."
End Sub

Sub MoveBelow ()

Application.ScreenUpdating = False

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
Dim rngToCopy As Range, rngToPaste As Range
Dim x As Long
Dim breakdown1
Dim breakdown As Worksheet: Set breakdown = wb.ActiveSheet

For x = 4 To 504 Step 6

    With wb.Sheets("Sheet1")
        breakdown1 = breakdown.Cells(9, x - 2)
    End With

    If IsEmpty(breakdown1) Then
        ' At this point when the macro meet again a empty cell
        ' it should keep moving from the same counted X
        ' but start the paste operation from 24 rows below.
    Else

        With wb.Sheets("Sheet1")
            Set rngToCopy = .Range(.Cells(4, x - 2), .Cells(24, x + 3))
            Debug.Print rngToCopy.Address
        End With

        With wb.Sheets("Sheet2")
            Set rngToPaste = .Range(.Cells(28, x - 2), .Cells(rngToCopy.Rows.Count + 3, x + 3))
            Debug.Print rngToPaste.Address
        End With

        rngToPaste = rngToCopy.Value
    End If

Next x

Application.ScreenUpdating = True
MsgBox "Valmis."
End Sub

因此，当宏将数据从工作表 1 复制/粘贴到工作表 2 并遇到一个空单元格时，它应该继续复制下一个可用数据，但将其粘贴到下面的 24 行。

--------在老问题下面。
我有一个 VBA，它正在打开和关闭该 INDEX 函数获取数据的文件。我的问题是这样的。 VBA 正在从包含完整路径的引用单元格中获取文件名。但是一些引用单元格是空白/零，然后正在运行的 VBA 停止并给我错误“找不到文件名”。有没有办法跳过它并进入下一步？

Sub HaeReseptiTiedot()

Dim myfile As String
Dim myfile1 As String
Dim myfile2 As String
Dim myfile3 As String
Dim myfile4 As String
Dim myfile5 As String
Dim myfile6 As String
Dim myfile7 As String
Dim myfile8 As String
Dim myfile9 As String


myfile = Cells(19, 4).Value
myfile1 = Cells(19, 9).Value
myfile2 = Cells(19, 14).Value
myfile3 = Cells(19, 19).Value
myfile4 = Cells(19, 24).Value
myfile5 = Cells(19, 29).Value
myfile6 = Cells(19, 34).Value
myfile7 = Cells(19, 39).Value
myfile8 = Cells(19, 44).Value
myfile9 = Cells(19, 49).Value

Application.ScreenUpdating = False


Workbooks.Open Filename:=myfile, UpdateLinks:=0
ActiveWorkbook.Close False
Sheets("Aputaulukko 2").Select
Range("D16:G30").Select
Selection.Copy
Sheets("Aputaulukko 3").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Workbooks.Open Filename:=myfile1, UpdateLinks:=0
ActiveWorkbook.Close False
Sheets("Aputaulukko 2").Select
Range("I16:L30").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Aputaulukko 3").Select
Range("G4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Answer 1

我发现处理这个问题的最好方法是使用“On Error”语句。您可以保持简单并使用 On Error Resume Next ，它告诉代码完全跳过错误并移至下一条语句(没有错误)。这样做的主要问题是它涵盖了所有错误，而不仅仅是您当前遇到的特定错误。很难知道是否发生错误/您的代码是否按预期运行。

另一个可以帮助避免上述问题的选项是使用以下内容:

On Error GoTo ErrH
    'Main Body of Your Code
    Exit Sub 'Use to avoid continuing on to the ErrH section.
ErrH:
    'Some method for handling the error, such as a message box or other notification.

对于小块代码，这通常不是必需的，但是当你开始组合你的 subs 和函数时，它可以成为救命稻草!

祝你好运!

编辑:如果工作表不需要这些空白，您可以/应该考虑删除这些空白。