Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    error trapping (2003)

    The following code collects data from project reports and under normal circumstances runs correctly.

    Sometimes project managers put text into cells where integers are required. Therefore I have included the error handling routine to record such instances. However the error handler does not properly trap error type 13 miss-match at which the code stops running

    Can somebody please advise on how to improve the error trap

    Thanks

    Alex

    Private Sub CollectProjectData(FileAddress As String)
    Dim i As Integer
    Dim j As Integer

    Dim k As Integer

    Dim FileShortName As String

    Workbooks.Open FileAddress
    FileShortName = ActiveWorkbook.Name
    ProjectData.BusinessAnalyst = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(4, 2).Value
    ProjectData.Director = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(3, 2).Value
    ProjectData.Engagementcode = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(5, 2).Value
    ProjectData.Manager = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(2, 2).Value
    ProjectData.ProjectName = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(1, 2).Value

    k = 1
    For i = 8 To 39
    For j = 2 To 13
    If Workbooks(FileShortName).Worksheets("resource Forecast").Cells(i, j).Value <> 0 Then
    On Error GoTo ErrorHandler
    ProjectData.Month(k) = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(7, j).Value
    ProjectData.StaffType(k) = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(i, 1).Value
    ProjectData.Days(k) = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(i, j).Value
    k = k + 1
    On Error GoTo 0
    End If
    Next j
    Next i
    Workbooks(FileShortName).Close savechanges:=False
    Exit Sub

    ErrorHandler:
    ErrorString = ErrorString & vbCr & "Error on " & vbTab & FileShortName & " Row " & i & vbTab & " Col " & j
    Resume Next
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error trapping (2003)

    I haven't looked at the code yet, but why don't you use Data | Validation to allow only integer values in the appropriate cells?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error trapping (2003)

    Hans

    Unfortunately the input data file is not under my full control and some rogue/old versions keep on finding their way back into the environment

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error trapping (2003)

    On which line (or lines) do you have problems with error 13?

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error trapping (2003)

    The error occurs on the last line of the following group of 3.

    ProjectData.Month(k) = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(7, j).Value
    ProjectData.StaffType(k) = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(i, 1).Value
    ProjectData.Days(k) = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(i, j).Value

    I have now updated the code as follows which traps most of the errors. However I will be grateful for your advice as to whether there is a better way to exclude the faulty data entirely

    If IsNumeric(Workbooks(FileShortName).Worksheets("res ource Forecast").Cells(i, j).Value) Then
    ProjectData.Days(k) = Workbooks(FileShortName).Worksheets("resource Forecast").Cells(i, j).Value
    Else
    ProjectData.Days(k) = 0
    ErrorString = ErrorString & vbCr & "Error on " & vbTab & FileShortName & " Row " & i & vbTab & " Col " & j
    End If

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error trapping (2003)

    You could use

    ProjectData.Days(k) = Val(Workbooks(FileShortName).Worksheets("resource Forecast").Cells(i, j).Value)

    Val returns 0 if its argument is not numeric. However, you'd still need the If IsNumeric test if you want to log errors.

Posting Permissions

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