excel - 错误 1400 : there is no such cell that satisfies criterias

Question

这是应用过滤器后复制数据的代码。

  Sub read_excel_file(path_to_current_work_book As String, path_to_destination_workbook As String)

Dim work_book As Object
Dim destination_workbook As Object
Dim i, m As Integer
Dim array_of_account_numbers() As Variant
Dim array_of_debit_or_credits() As Variant
Dim current_sheets As Worksheet
Dim buf_rng As Range

array_of_account_numbers = Array("1400", "1401", "1402", "1403", "1410", "1411", "1412", "1413", "1414", "1420", "1421", "1422", "1423", "1424", "1430", "1440")
array_of_debit_or_credits = Array("10", "11", "20", "21")

Application.DisplayAlerts = False
Application.Visible = True

Set work_book = Workbooks.Open(path_to_current_work_book)
Set destination_workbook = Workbooks.Open(path_to_destination_workbook)

destination_workbook.Sheets(1).Cells(1, 1).Value = "Debit(10,11)/Credit(20, 21)"
destination_workbook.Sheets(1).Cells(1, 2).Value = "Balance account number"
destination_workbook.Sheets(1).Cells(1, 3).Value = "Currency code"
destination_workbook.Sheets(1).Cells(1, 4).Value = "Resident"
destination_workbook.Sheets(1).Cells(1, 5).Value = "Amount"
destination_workbook.Sheets(1).Cells(1, 6).Value = "Date"

m = 2
For i = 1 To work_book.Worksheets.Count
    With work_book.Sheets(i)
        If (.UsedRange.Rows.Count > 1) Then
            .UsedRange.AutoFilter Field:=1, Criteria1:=array_of_debit_or_credits, Operator:=xlFilterValues
            .UsedRange.AutoFilter Field:=2, Criteria1:=array_of_account_numbers, Operator:=xlFilterValues
            m = destination_workbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).row + 1
            .AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy destination_workbook.Sheets(1).Range("A" & m)
        End If
    End With
Next i
work_book.Close savechanges:=False
destination_workbook.Close savechanges:=True
End Sub

它会产生以下错误(当自动过滤范围(不包括标题)为空时):“错误 1400:没有满足条件的单元格”。

.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy destination_workbook.Sheets(1).Range("A" & m)

我该如何处理这个错误？

Answer 1

将其设置为一个范围，然后检查该范围是否为Nothing

试试这个(未经测试)

Dim Rng as Range

'
'~~> Rest of your code
'

On Error Resume Next
Set Rng = .AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count _
          - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not Rng Is Nothing Then
    'rng.copy... blah blah
End If