on error test inside a loop not testing each time through loop

  • I have a selection.find inside of a for loop. Prior to the selection.find I have an on error goto. This process works fine up to and including the first error instance. After that, the next error instance causes a run time error (instead of going to).


    A little background - what I'm doing here is isolating instances on 1 sheet (test) against another sheet (control). If the value exists on the control I copy the relevent data to the test sheet. Each day the old test becomes the new control and a new test is compared.


    There's a few comments with no code, they are just my notes on the process, I haven't gotten past this error problem to complete it.


    It acts like the 'on error' test is only performed once, and not each time through the loop....
    So I tried setting the error to nothing, or 0, directly after e:, but it didn't work, I thought maybe clear the error buffer and force the code to test it each time ... ?



    I really appreciate your help


    /Nick

    <b><font color="#23069E">_NFio_</font></b>

  • Re: on error test inside a loop not testing each time through loop


    I believe you have to reset your error flag.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: on error test inside a loop not testing each time through loop


    thanks, I tried it but it behaves the same ... any other ideas?

    <b><font color="#23069E">_NFio_</font></b>

  • Re: on error test inside a loop not testing each time through loop


    Hi,


    How about catching the not found more gracefully.[vba]Sub x()

    Dim rngFind As Range

    On Error Resume Next
    For i = 1 To myLastDataRowTest
    blah
    blah
    ...

    Set rngFind = Nothing
    Set rngFind = Sheets(sheetsControl).Columns("E:E").Find(What:=newStock, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
    , SearchFormat:=False)
    If Not rngFind Is Nothing Then
    'debug
    MsgBox "Found: " & newStock, vbCritical, ""
    ' check the control account against the test account
    'if same copy the text on the control sheet
    'paste the text to the test sheet
    'return to test sheet and test range
    End If
    Sheets(sheetsTest).Select
    Range(myReturnAddress).Select
    Selection.Offset(-1, 0).Select
    Next
    End Sub[/vba]

  • Re: on error test inside a loop not testing each time through loop


    great!! worked like a charm .. and I got to learn a bit about vba's double negative's, kewl.


    thanx!

    <b><font color="#23069E">_NFio_</font></b>

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!