Excel search error

[peterd51]

Hi,

I pick up a name from sheet one, switch to sheet two and search for the name.

I then copy data from alongside the name and paste it back into sheet one.

No problem until it doesn't find the name on sheet two where it crashed.

So I added error checking but now it works OK until the first missing name on sheet two and then errors on every name after that.

What have I missed please:

************************
On Error GoTo ErrSub

for n=1 to rowcount

Skip = "no"

xfer1 = "A" & Trim(Str)
Range(xfer1).Select
xfer2 = ActiveCell.Value

Sheets(TWO).Select

Columns("A:A").Select
Range("A2").Select

Selection.Find(What:=xfer2, After:=ActiveCell, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

' Ok until first selection not found then rejects every selection afterwards
' gives error 91, something about 'with / end with'

If Skip = "no" Then
'rest of program

Sheets(ONE).Select
end if

next n

exit sub

ErrSub:

myResponse = MsgBox("Error in " & xfer2, vbOKOnly)

Sheets(ONE).Select

Skip = "yes"

Resume Next

end sub
***********************

Regards
Peter

 

[UnsolvedCoding]

Try changing After:=ActiveCell to use cell A1

 

[SkipVought]

I advise against using activeaything

Sub test()
    Dim rFound As Range
    
    On Error GoTo ErrSub
    
    For n = 1 To RowCount
    
      Skip = "no"
       
      xfer2 = Cells(n, "A")
        
        With Sheets(TWO).columns(1)
            Set rFound = .Find(What:=xfer2, After:=.cells(1,1), LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False)
        
            If Not rFound Is Nothing Then
                'do this if FOUND
            Else
                'do this if NOT FOUND
                Skip = "yes"
       
            End If
        
        End With
    Next n
    
    Exit Sub
    
ErrSub:
    
    myResponse = MsgBox("Error in " & xfer2, vbOKOnly)
    
    Sheets(ONE).Select
    
    Skip = "yes"
    
    Resume Next

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[peterd51]

Hi,

sorry for the late response, I got tied up with work as my contract was ending.

UnsolvedCoding: thanks but I tried that and it didn't work.

Skipvought: thanks, I'll play around with your suggestion.

Regards
Peter