Error Handle problem

[Len]

Hi,

I tried to copy some filtered rows. If there is no rows to copy, it
will encounter: Run-time error '1004', No Cells were found. ie the
code stops at "result.SpecialCells(xlCellTypeVisible).Copy"
Then I change the code to "If result Is Nothing Then " and continue,
It smoothly completes the process.
However, it fails to copy when there are rows to copy ie the code
skips copying the rows and reach at End If

Codes Extract

Sheets(1).Select
Dim Lrow As Long
Dim result As Range
Lrow = Range("A" & Rows.Count).End(xlUp).Row
Set result = Range("A2:G" & Lrow + 1)
Range("K1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=11, Criteria1:=">0", _
Operator:=xlOr, Criteria2:="<0"
Range("D1").Select
Selection.AutoFilter Field:=4, Criteria1:="=*void*", _
Operator:=xlAnd
If Not result Is Nothing Then
result.SpecialCells(xlCellTypeVisible).Copy
Sheets(2).Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If

Any helps will be much appreciated and thanks in advance

Regards
Len

 

[Paul Robinson]

Hi
I think Nothing refers to a variable with no assigned value (could be
wrong!) so isn't appropriate here.
You could simply ride over the error and ignore it using "on error
resume next" as below. If an error occurs nothing happens, which is
what you want anyway.
Sheets(1).Select
Dim Lrow As Long
Dim result As Range
Lrow = Range("A" & Rows.Count).End(xlUp).Row
Set result = Range("A2:G" & Lrow + 1)
Range("K1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=11, Criteria1:=">0", _
Operator:=xlOr, Criteria2:="<0"
Range("D1").Select
Selection.AutoFilter Field:=4, Criteria1:="=*void*", _
Operator:=xlAnd
On error resume next
result.SpecialCells(xlCellTypeVisible).Copy
Sheets(2).Select
Range("A1").Select
ActiveSheet.Paste
On error goto 0
Application.CutCopyMode = False

regards
Paul

 

[Len]

Hi Paul,

Thanks for your prompt reply and your suggestion
but I still got the same error
I'm not sure whether the same error is due to the codes which have "On
Error Resume Next with On Error Goto 0 " and also another "On Error
GoTo NextProcess with NextProcess:" used in the earlier parts of codes


Thanks and Regards
Len

 

[Paul Robinson]

Hi
It must be something that is not contained within the error
statements, otherwise you would get no error message (though there may
be an error of course, just no message).
regards
Paul

 

[Len]

Hi Paul,

Thanks for your advice

After I worked around to debug the codes, there was an error that
there is no cells to copy if no cells were found but it still not able
to skip the line of codes and remains at
"result.SpecialCells(xlCellTypeVisible).Copy"

Then I tried to separate the codes which contains "On Error GoTo
NextProcess " and "On Error Resume Next " into different sub
procedures, it works perfectly.

Does it mean that the codes can not have both "On Error GoTo
NextProcess " and "On Error Resume Next " within the same sub
procedure ?

Regards
Len