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

Thread: On Error Goto Blue Does not work

  1. #1
    Board Regular
    Join Date
    May 2011
    Location
    United States of America, Maryland, Riverdale
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default On Error Goto Blue Does not work

    Hi everyone, I have these lines of codes:
    Code:
    FoundValue = MySheet.Cells.Find(What:="Deductions from Net Pay", After:=Cells(1, 1), LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False).Row
    
    On Error Resume Next
    FoundValue1 = MySheet.Cells.Find(What:="Additions to Net Pay", After:=Cells(1, 1), LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False).Row
    RowNum4 = FoundValue
            
                    On Error GoTo Blue
                    Do Until MySheet.Cells(RowNum4, 2) = "Total Deductions from Net Pay"
            
            
                       RowNum4 = RowNum4 + 1
                       CellContent = MySheet.Cells(RowNum4, 3)
                       
                       If CellContent = "401(K) Loan Repay" Then '1
                            TransferSheet.Cells(NextRow, 45) = MySheet.Cells(RowNum4, ColNum + 4) * -1
                        Else
                       End If
                        If CellContent = "Moneys Owed  to Company" Then '2
                            TransferSheet.Cells(NextRow, 46) = MySheet.Cells(RowNum4, ColNum + 4) * -1
                        Else
                       End If
                    Loop
           
    Blue:
               
              rownum5 = FoundValue1
              
              
              On Error GoTo Green
                    Do Until MySheet.Cells(rownum5, 2) = "Total Additions to Net Pay"
            
            
               
                       rownum5 = rownum5 + 1
                       CellContent = MySheet.Cells(rownum5, 3)
                       
                       If CellContent = "Reimburse-Educational Assist" Then '1
                            TransferSheet.Cells(NextRow, 47) = MySheet.Cells(rownum5, ColNum + 4) * -1
                        Else
                       End If
                        'If CellContent = "Moneys Owed  to Company" Then '2
                            'TransferSheet.Cells(NextRow, 46) = MySheet.Cells(RowNum5, ColNum + 4) * -1
                        'Else
                       'End If
                    Loop
               
    2:
    Green:
    The code is supposed to find two phrase and use those phrases in the Do Until Loop to transfer records if found otherwise I want the code to do nothing.

    Thanks in advance.
    Last edited by RoryA; Aug 4th, 2016 at 10:17 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: On Error Goto Blue Does not work

    You're not resetting the error: On Error WTF? | Excel Matters

    I'm not really clear on why you are trying to handle errors in this way?

  3. #3
    Board Regular
    Join Date
    May 2011
    Location
    United States of America, Maryland, Riverdale
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Goto Blue Does not work

    Quote Originally Posted by RoryA View Post
    You're not resetting the error: On Error WTF? | Excel Matters

    I'm not really clear on why you are trying to handle errors in this way?
    The phrases are not in all the worksheets and so when Find did not found a phrase the value assign to FoundValue is Empty. If FoundValue is empty I want it to skip certain lines of code.

  4. #4
    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: On Error Goto Blue Does not work

    The recommended way is to check if the value is found like this:
    Code:
    Dim rgFound as Range
    Set rgFound = MySheet.Cells.Find(What:="Deductions from Net Pay", After:=Cells(1, 1), LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)
    If not rgFound Is nothing then 
       FoundValue = rgFound.Row
    Else
       Exit Sub
    End If

  5. #5
    Board Regular
    Join Date
    May 2011
    Location
    United States of America, Maryland, Riverdale
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Goto Blue Does not work

    Thanks RoryA. It works.

  6. #6
    Board Regular
    Join Date
    May 2011
    Location
    United States of America, Maryland, Riverdale
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Goto Blue Does not work

    Thanks RoryA, I total messed up here.

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
  •