Results 1 to 3 of 3

Error handling using cells.find

Threaded View

maxwell13 Error handling using... 10-12-2008, 12:37 AM
Leith Ross Hello Jeff, The Find... 10-12-2008, 01:39 AM
maxwell13 Hi Leith Thanks for your... 10-12-2008, 05:02 PM
  1. #1
    Registered User
    Join Date
    04-05-2007
    Location
    Sydney Australia
    Posts
    48

    Error handling using cells.find

    Hi All

    I am having a problem running the following code when the text i am searching for is not found.
    The On Error GoTo works if there is only one instance of the text not being found but crashes if there is a second.
    I have tried adapting code found here http://www.excelforum.com/excel-prog...ght=cells.find but can't get it to work exactly as i would like.
    Here is an extract of my code. There are about 12 different cells.find in total.



    Sub Extract()
    '
    
    
    
    
        Application.ScreenUpdating = False
    Range("a1").Select
    On Error GoTo Outlet
    Cells.Find(What:="Outlet Report", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
      On Error GoTo 0
        Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 4), Array(6, 4)), _
            TrailingMinusNumbers:=True
        ActiveCell.Range("A1:f1").Select
        Selection.Copy
        ActiveCell.Offset(0, 12).Range("A1").Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, -12).Range("A1").Select
        Application.CutCopyMode = False
    Outlet:
    On Error GoTo AccountDeposits
        Cells.Find(What:="Outlet:", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
         On Error GoTo 0
        Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
            True
        ActiveCell.Range("A1:d1").Select
        Selection.Copy
        ActiveCell.Offset(0, 12).Range("A1").Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, -12).Range("A1").Select
        Application.CutCopyMode = False
        
     
    AccountDeposits:
    On Error GoTo PMTick
        Cells.Find(What:="Account Deposits", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            On Error GoTo 0
        Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
            True
        ActiveCell.Range("A1:d1").Select
        Selection.Copy
        ActiveCell.Offset(0, 12).Range("A1").Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, -12).Range("A1").Select
        Application.CutCopyMode = False
    PMTick:
    Application.ScreenUpdating = True
    
    End Sub
    Any help is greatly appreciated

    Thanks

    Jeff
    Last edited by maxwell13; 10-12-2008 at 05:04 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Run-time error '91' when 'On error goto' and cells.find
    By bjoern in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2008, 07:27 PM
  2. Excell (Office 2003): how do I skip Cells.Find within a macro
    By bjoern in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-07-2008, 11:32 AM
  3. Cells.Find and PivotTables
    By Notaguru in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-05-2008, 05:12 PM
  4. Cells.Find Specifics
    By mpeplow in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-06-2007, 01:02 PM
  5. vba cells.find problem
    By jeternyn1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2007, 02:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1