Error Handling

[Jacques Labuschagne]

Hi

Hope soembody can help...

The below code works for error handling, but the issue is that if it goes into the error part of the program is return and excutes the previous loop again from where there was no error... Think there is something wrong when I execute the Resume next, but not sure how to solve this.. Any help please...

Sub FindText()
        On Error GoTo ErrorHandler
        
              
                 Worksheets(1).Cells.Find(What:=te.TxtName, After:=ActiveCell, LookIn:=xlFormulas, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                 MatchCase:=False, SearchFormat:=False).Activate
                
                 MsgBox "Item you looking for " & ActiveCell.Column & " " & ActiveCell.Row & " " & TxtName
                    
        Exit Sub
        
        
ErrorHandler:
        
        
        MsgBox "Not Found"
        
        Resume Next
End Sub

Regards,
Jacques

 

[JoeMo]

If you have posted the entire code, it appears that you can just delete the Resume Next line.

 

[Scott Huish]

A better way is to not use Activate. Set your results to a range variable and then check to see if that variable contains a range. Here is simple example, searching for the word "test"

Sub test()
Dim c As Range
Set c = Sheets(1).Cells.Find("test")
If Not c Is Nothing Then
    MsgBox "test found in Column " & c.Column & " and Row " & c.Row
Else
    MsgBox "Not Found"
End If
End Sub

 

[Jacques Labuschagne]

Hi All

Thanks learned some additional tricks again with the find instruction....

Got one more question... Why will the below not work. Trying to search through a Row of records and do soemthing till the value = "end".

Sub OrderRows()
Dim i As Integer
i = 0

While Worksheets("Pricelist").Cells(i, 2).Value <> "end"

Worksheets("Sheet2").Cells(i, 1) = Worksheets("Pricelist").Cells(i, 2)
Worksheets("Sheet2").Cells(i, 2) = Worksheets("Pricelist").Cells(i, 3)
i = i + 1

Wend

End Sub

Regards,

 

[Scott Huish]

Your structure could first of all be simplified with either a For Next or a For Each Next Loop. But why not just use Find to find the word End on sheet PriceList and copy that data from the Pricelist to Sheet2? Also variables that deal with rows should always be declared as Long because rows can go outside the range of the Integer Data Type.

Sub test()
Dim i As Range, ws As Worksheet
Set ws = Worksheets("PriceList")
Set i = ws.Range("A:A").Find("end", Cells(Rows.Count, 1))
If i Is Nothing Then Exit Sub
ws.Range("A1:B" & i.Row - 1).Copy
Worksheets("Sheet2").Range("A1").PasteSpecial
Application.CutCopyMode = False
End Sub