+ Reply to Thread
Results 1 to 2 of 2

Problem with error handling on second error - please explain

  1. #1
    Registered User
    Join Date
    05-15-2013
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    32

    Problem with error handling on second error - please explain

    Hi,

    I have a code with selects a list and finds a date. Then it works in that column. In case a particular date is missing, the code moves to an error handler which adds 1 to the date to that the code looks for the next date. This works the first time but fails the second time. Why is this happening? The code is below.

    <code>
    Dim dte As Date

    Sheets("Inputs").Select
    Range("AO6").Select

    Do Until IsEmpty(ActiveCell) = True

    If IsEmpty(ActiveCell.Offset(1, 0)) = False Then
    ActiveCell.Offset(0, 2).Select

    Else:

    dte = ActiveCell

    b = ActiveCell.Column

    Sheets("Inputs").Range("BP7").Activate

    Sheets("Records").Select
    Range("E7").Select
    Range(Selection, Selection.End(xlToRight)).Select
    repeat1:
    On Error GoTo errhandler

    Selection.Find(What:=dte, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select

    Do Until Month(ActiveCell.Offset(-1, 0)) = Month(dte) = False

    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Inputs").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Selection.End(xlDown).Offset(1, 0).Select
    Sheets("Records").Select
    ActiveCell.Offset(0, 1).Activate

    Loop

    Application.CutCopyMode = False

    Sheets("Inputs").Select
    Range("BP7").Activate

    Range("BP7").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Cells(7, b).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=Array(1), Header:= _
    xlNo
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.FormulaR1C1 = "=COUNTIF(R7C68:R10000C68,RC[-1])"
    Selection.Copy
    Range(ActiveCell, ActiveCell.Offset(ActiveCell.Offset(-2, -1).Value - 1, 0)).Select
    ActiveSheet.Paste
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

    ActiveCell.Offset(-1, 1).Select

    End If

    Sheets("Inputs").Range("BP7:BP10000").ClearContents

    Loop


    Exit Sub

    errhandler:

    dte = dte + 1
    GoTo repeat1

    End Sub
    </code>

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Problem with error handling on second error - please explain

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    If posting code please use code tags, see here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. problem with error handling
    By joelhuang in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2013, 06:21 AM
  2. problem with if statements and error handling
    By papermoon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-20-2010, 11:55 AM
  3. Problem with macro containing Vlookup - error handling??
    By NateW in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2007, 01:12 PM
  4. Error Handling - On Error GoTo doesn't trap error successfully
    By David in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-16-2006, 02:10 PM
  5. Error Handling problem
    By Brassman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-24-2005, 11:10 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