Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Error Handling Issues

  1. #1
    Board Regular
    Join Date
    Apr 2016
    Location
    Belo Horizonte, Brazil
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Error Handling Issues

    Hello boys and girls. I'am having a problem I don't seem to be able to track down.

    I have na excel file that is na extraction from another software. The software is used to control people timesheet, so it has 100+ pages, that when extracted to excel it gives me all this pages one beneath the other repeatig every time the headers and the footnotes.

    The pages are all the same template, so what I'm trying to do a loop to find some strings that always appears in the headers and delete the row where it appears and so on. After deleting all the headers I want to do it again for the footnotes leaving me only with the actual data, that I can work with.

    I've got this code so far. The first block of finding and deleting works just fine, but the second one doesn't. I'm looping through the file using error handling in the find function, but when it erases all the rows that contain the string "user name:" the code stops in the line intEndPageRow = Cells.Find("User name:").Row
    Code:
    Sub OrganizarPlanilha()
    
    Dim intTopRow As Integer
    Dim intBottomRow As Integer
    Dim intEndPageRow As Integer
    
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Cells(1, 1).Select
    
    Delete:
        On Error GoTo Continue
        intTopRow = Cells.Find("User timesheet").Row
        intBottomRow = Cells.Find("Date:").Row
        Range(intTopRow & ":" & intBottomRow).Select
        Selection.Delete Shift:=xlUp
        GoTo Delete
    
    Continue:
    
        On Error GoTo Following
        intEndPageRow = Cells.Find("User name:").Row
        Range(intEndPageRow & ":" & intEndPageRow).Select
        Selection.Delete Shift:=xlUp
        GoTo Continue
    
    Following:
    
    
    End Sub
    Can you help me out?
    Last edited by marcelocbd; Jul 5th, 2016 at 08:31 AM.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Error Handling Issues

    This is the reason for your error handlers not working: On Error WTF? | Excel Matters

    However, you don't really need an error handler here. You simply test whether the search text was found before you try and do anything with the found cell - for example:

    Code:
    Sub OrganizarPlanilha()
    
        Dim rStart                As Range
        Dim rEnd                  As Range
    
        Rows("1:1").Insert Shift:=xlDown
        Cells(1, 1).Select
    
        Set rStart = Cells.Find("User timesheet")
        If Not rStart Is Nothing Then
            Do
                Set rEnd = Cells.Find("Date:")
                If Not rEnd Is Nothing Then
                    Range(rStart, rEnd).Delete Shift:=xlUp
                    Set rStart = Cells.Find("User timesheet")
                Else
                    Exit Do
                End If
            Loop While Not rStart Is Nothing
        End If
    
        Set rStart = Cells.Find("User name:")
        If Not rStart Is Nothing Then
            Do
                rStart.EntireRow.Delete Shift:=xlUp
                Set rStart = Cells.Find("User name:")
            Loop While Not rStart Is Nothing
        End If
    End Sub

  3. #3
    Board Regular
    Join Date
    Apr 2016
    Location
    Belo Horizonte, Brazil
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error Handling Issues

    Thank you so much RoryA.

    Very clarifying link, apparently i knew nothing about error handling.

    I'll put the code you gave me into action.
    Last edited by marcelocbd; Jul 5th, 2016 at 09:03 AM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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