Error Handling

Jacques Labuschagne

Board Regular
Joined
Feb 5, 2012
Messages
58
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...

Code:
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you have posted the entire code, it appears that you can just delete the Resume Next line.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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"

Code:
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
 
Last edited:

Jacques Labuschagne

Board Regular
Joined
Feb 5, 2012
Messages
58
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,906
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top