Solved How to report problematic date values during error handling

January 3, 2019 at 01:46:46
Specs: Windows 10
I've just been helped via this forum to produce the following code which copies and pastes data where a date is about to move into an older case date bracket:

Sub DateBrackets()

'Clear date bracket cases from "Workload" sheet, Leave Header Row
  Sheets("Workload").Range("AB3:" & Range("AE3").End(xlDown).Address).ClearContents

Application.ScreenUpdating = False

'Determine Last Row with data on Sheet 1
  lastSrcRw = Sheets("Data").Cells(Rows.Count, "F").End(xlUp).Row

'Loop through Column F, Status must be "Work in progress"
    For myDates = 2 To lastSrcRw
     If Sheets("Data").Cells(myDates, "S") = "Work in progress" Then
'Calculate Case Age from Column E,
      caseAge = Date - Sheets("Data").Cells(myDates, "Q")
'Set New Bracket based on Case Age
      If caseAge < 181 Then
        If 180 - caseAge < 28 Then
           new_Bracket = "180 Plus"
        ElseIf 91 - caseAge < 14 Then
           new_Bracket = "91 to 180"
        ElseIf 30 - caseAge < 7 Then
           new_Bracket = "31 to 90"
        End If
      End If

 'If new Bracket required, copy data to Sheet 2
      If new_Bracket <> "" Then
        With Sheets("Workload")
          nxtDstRw = .Cells(Rows.Count, 28).End(xlUp).Row + 1
         .Cells(nxtDstRw, "AB") = Sheets("Data").Cells(myDates, "F")
         .Cells(nxtDstRw, "AC") = Sheets("Data").Cells(myDates, "R")
         .Cells(nxtDstRw, "AD") = Sheets("Data").Cells(myDates, "Q")
         .Cells(nxtDstRw, "AE") = new_Bracket
        End With
'Clear New Bracket flag
      new_Bracket = ""
      End If
     End If
End Sub

The source data is copied from a sharepoint site and occasionally some of the dates have been input incorrectly. For example, I've seen 14/12/2018 input as 14/12/21018. The dates are entered in column Q of the "Data" worksheet in my spreadsheet. The date input errors can be a problem when assigning a value to the caseAge variable which can cause Type Mismatch errors to halt the macro.

I'd like to add some error handling to the above code that will allow the macro to move onto the next "Date Assigned", but also show the reference numbers for the problematic dates (entered in column F of the "Data" worksheet) that need fixing. I don't know whether the reference numbers could pop up in a message box at the end of the macro, or would need to be copied to an "Errors" tab that I could add into the workbook? Perhaps you could advise on which would be best/possible?

The reference numbers that I'd like to be reported as part of the error handling are integers. If you need further information please let me know.

Many thanks.

message edited by ScottV

See More: How to report problematic date values during error handling

Report •

January 3, 2019 at 07:01:36
✔ Best Answer
First: It appears that you have modified the code to match the columns in your sheets, your sheet names, etc.

If you are going to modify the code, then you should modify the Comments also. It's really confusing for readers to see things like this, where the Comment references Column E, but the Instruction references Column Q.

'Calculate Case Age from Column E,
      caseAge = Date - Sheets("Data").Cells(myDates, "Q")

Trust me, you may be able to ignore the differences today while the code is fresh in your mind, but wait a year or so until you go back in to make a modification. You'll try to use the comments to remind yourself of what the code is supposed to be doing and you'll get all confused because the Instructions don't match the Comments. Comments are used to help the reader understand the code and very often that reader is also the author. (In fact, I'm already getting confused while trying to set a test workbook in order to answer this latest question.)

As far as your issue with invalid dates, the best solution would be to eliminate the problem at the source so that you don't have to deal with it in your code.

Can the "data entry point" be set up to only accept valid dates? In Excel, there a multiple ways to prevent entries such as 14/12/21018. Can the same thing be done at the source?

If you are stuck with receiving bad data, then the code could validate the date and take action based on what is in the cell. This snippet shows how that could be done.

'Loop through Column S, Status must be "Work in progress"
    For myDates = 2 To lastSrcRw
     If Sheets("Data").Cells(myDates, "S") = "Work In Progress" Then

'Validate date in Column Q
       If Not IsDate(Sheets("Data").Cells(myDates, "Q")) Then
        '---> Do What You Want To Do With Invalid Dates <---


'Calculate Case Age from Column Q,
        caseAge = Date - Sheets("Data").Cells(myDates, "Q")

       End If
     End If

message edited by DerbyDad03

Report •

January 3, 2019 at 07:08:25
Thanks for the prompt about the comments. I completely forgot about those...

I can't control receiving the bad data unfortunately, so I'll have a play with your suggestion about the validation.

Report •

January 4, 2019 at 07:10:56
I used your snippet and it helped perfectly. Thank you.

Since your last post, my users have changed their requirements a bit when I showed them what I'd done which has thrown up some new issues. I've solved most of them but there are a couple still to address which I can't crack so I'll post these in a new thread otherwise I think it would get confusing for anyone else following this.

Hope that's ok? Thanks as always.

Report •
Related Solutions

Ask Question