error handler only catches once

[said76]

Hi,

I have the following codes that would do copy and paste between one worksheet and another sheet in the same workbook. However, the problem I am having is catching the error. The error handler seems to work only once. Is there a way to reset it or perhaps I have done it the wrong way.

For counter = 1 To WorkSheets.Count
   
    Worksheets(counter).Activate
    
    MyCol = 1

    MyRowIndex = 1
    
    'On Error GoTo Errhandler
    Do While Worksheets(counter).Range("A" & MyRowIndex) <> "" 
        On Error GoTo Errhandler
        'On Error Resume Next

        If Worksheets(counter).Range("J" & MyRowIndex) = "Cap" Then
  
            Worksheets(counter).Range("J" & MyRowIndex).EntireRow.Copy
            
            Sheets("Sheet3").Select
            Range("A" & MyCol).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
         
            Worksheets("Sheet3").Range("O" & MyCol) = Worksheets(counter).Name
            MyCol = MyCol + 1      
        End If
        
sdfs:
        MyRowIndex = MyRowIndex + 1
    
    Loop

Errhandler:
    If Err.Number = 13 Then
        GoTo sdfs
    'Else
        'Resume Next
    End If

Next i

What this code does is to go through each of the worksheets and in each of the sheets, do the loop in search of a particular value in column J. The reason I need to apply the error handler here is in some of the sheets, there are #N/A value. So I need to be able to skip that and continue to loop through down the column.

Any help would be greatly appreciated.

Thank you

 

[RoryA]

You cannot use Goto to exit an error handler - you have to use Resume, otherwise all the code that runs afterwards is treated as being part of the error handler routine, and any error that occurs in an error handler causes a run time error.

However, here it would be simpler to add an IsError test or check the cell's Text property rather than Value. You can also use CStr to convert the Value to text and then you won't get a type mismatch error, even if the cell contains an error value.

 

[said76]

It works now. Thank you for your help.