sumCAMSbyYear macro

[samlrun]

Medium Level Issue

This is the macro that creates the Parameters workbook and it is the final process of the RGUI. It is found in the RGUI module. It is written in hard code so it expects certain columns to exist. I commented out the columns we don't have, but it may be better to modify this code so it is more flexible.

    If SegYear = CrashYear Then                                         'FLAGGED: This code assumes certain columns exist.
          'Total crashes
          Sheets(datasheet).Cells(SegRow + i, TotCrashCol) = Sheets(datasheet).Cells(SegRow + i, TotCrashCol) + 1
          
          'Severe crashes
          If CrashSev >= 3 Then
            Sheets(datasheet).Cells(SegRow + i, SevCrashCol) = Sheets(datasheet).Cells(SegRow + i, SevCrashCol) + 1
          End If
          
          'Head on collisions
          If Sheets(CrashSheet).Cells(CrashRow, MannerCollisionCol) = 3 Then
             Sheets(datasheet).Cells(SegRow + i, HeadOnCol) = Sheets(datasheet).Cells(SegRow + i, HeadOnCol) + 1
          End If
                              
          'Work zone related
          'If Sheets(CrashSheet).Cells(CrashRow, WorkZoneCol2) = "Y" Then
          '    Sheets(datasheet).Cells(SegRow + i, WorkZoneCol1) = Sheets(datasheet).Cells(SegRow + i, WorkZoneCol1) + 1
          'End If
          
          'Pedestrian Involved
          If Sheets(CrashSheet).Cells(CrashRow, PedInvCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, PedInvCol1) = Sheets(datasheet).Cells(SegRow + i, PedInvCol1) + 1
          End If
          
          'Bicyclist Involved
          If Sheets(CrashSheet).Cells(CrashRow, BikeInvCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, BikeInvCol1) = Sheets(datasheet).Cells(SegRow + i, BikeInvCol1) + 1
          End If
          
          'Motorcycle Involved
          If Sheets(CrashSheet).Cells(CrashRow, MotInvCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, MotInvCol1) = Sheets(datasheet).Cells(SegRow + i, MotInvCol1) + 1
          End If
          
          'Improper restraint
          'If Sheets(CrashSheet).Cells(CrashRow, ImpResCol2) = "Y" Then
          '    Sheets(datasheet).Cells(SegRow + i, ImpResCol1) = Sheets(datasheet).Cells(SegRow + i, ImpResCol1) + 1
          'End If
          
          'Unrestrained
          If Sheets(CrashSheet).Cells(CrashRow, UnrestCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, UnrestCol1) = Sheets(datasheet).Cells(SegRow + i, UnrestCol1) + 1
          End If
          
          'DUI
          If Sheets(CrashSheet).Cells(CrashRow, DUICol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, DUICol1) = Sheets(datasheet).Cells(SegRow + i, DUICol1) + 1
          End If
          
          'Aggresive Driving
          If Sheets(CrashSheet).Cells(CrashRow, AggressiveCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, AggressiveCol1) = Sheets(datasheet).Cells(SegRow + i, AggressiveCol1) + 1
          End If
          
          'Distracted Driving
          If Sheets(CrashSheet).Cells(CrashRow, DistractedCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, DistractedCol1) = Sheets(datasheet).Cells(SegRow + i, DistractedCol1) + 1
          End If
          
          'Drowsy Driving
          If Sheets(CrashSheet).Cells(CrashRow, DrowsyCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, DrowsyCol1) = Sheets(datasheet).Cells(SegRow + i, DrowsyCol1) + 1
          End If
          
          'Speed related
          If Sheets(CrashSheet).Cells(CrashRow, SpeedCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, SpeedCol1) = Sheets(datasheet).Cells(SegRow + i, SpeedCol1) + 1
          End If
          
          'Intersection related
          If Sheets(CrashSheet).Cells(CrashRow, IntRelCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, IntRelCol1) = Sheets(datasheet).Cells(SegRow + i, IntRelCol1) + 1
          End If
          
          'Adverse weather
          If Sheets(CrashSheet).Cells(CrashRow, WeatherCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, WeatherCol1) = Sheets(datasheet).Cells(SegRow + i, WeatherCol1) + 1
          End If
          
          'Adverse roadway surface conditions
          If Sheets(CrashSheet).Cells(CrashRow, AdvRoadCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, AdvRoadCol1) = Sheets(datasheet).Cells(SegRow + i, AdvRoadCol1) + 1
          End If
          
          'Roadway geometry related
          If Sheets(CrashSheet).Cells(CrashRow, RoadGeomCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, RoadGeomCol1) = Sheets(datasheet).Cells(SegRow + i, RoadGeomCol1) + 1
          End If
          
          'Wild animal related
          If Sheets(CrashSheet).Cells(CrashRow, WAnimalCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, WAnimalCol1) = Sheets(datasheet).Cells(SegRow + i, WAnimalCol1) + 1
          End If
          
          'Domestic animal related
          If Sheets(CrashSheet).Cells(CrashRow, DAnimalCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, DAnimalCol1) = Sheets(datasheet).Cells(SegRow + i, DAnimalCol1) + 1
          End If
          
          'Roadway departure
          If Sheets(CrashSheet).Cells(CrashRow, RoadDepartCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, RoadDepartCol1) = Sheets(datasheet).Cells(SegRow + i, RoadDepartCol1) + 1
          End If
          
          'Overturn/rollover
          If Sheets(CrashSheet).Cells(CrashRow, OverturnCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, OverturnCol1) = Sheets(datasheet).Cells(SegRow + i, OverturnCol1) + 1
          End If
          
          'Commercial motor vehicle involved
          If Sheets(CrashSheet).Cells(CrashRow, CommVehCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, CommVehCol1) = Sheets(datasheet).Cells(SegRow + i, CommVehCol1) + 1
          End If
          
          'Interstate highway
          If Sheets(CrashSheet).Cells(CrashRow, InterstateCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, InterstateCol1) = Sheets(datasheet).Cells(SegRow + i, InterstateCol1) + 1
          End If
          
          'Teenage driver involved
          If Sheets(CrashSheet).Cells(CrashRow, TeenCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, TeenCol1) = Sheets(datasheet).Cells(SegRow + i, TeenCol1) + 1
          End If
          
          'Older driver involved
          If Sheets(CrashSheet).Cells(CrashRow, OldCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, OldCol1) = Sheets(datasheet).Cells(SegRow + i, OldCol1) + 1
          End If
          
          'Urban county
          'If Sheets(CrashSheet).Cells(CrashRow, UrbanCol2) = "Y" Then
          '    Sheets(datasheet).Cells(SegRow + i, UrbanCol1) = Sheets(datasheet).Cells(SegRow + i, UrbanCol1) + 1
          'End If
          
          'Night dark condition
          If Sheets(CrashSheet).Cells(CrashRow, NightCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, NightCol1) = Sheets(datasheet).Cells(SegRow + i, NightCol1) + 1
          End If
          
          'Single vehicle
          If Sheets(CrashSheet).Cells(CrashRow, SingleVehCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, SingleVehCol1) = Sheets(datasheet).Cells(SegRow + i, SingleVehCol1) + 1
          End If
          
          'Train involved
          If Sheets(CrashSheet).Cells(CrashRow, TrainCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, TrainCol1) = Sheets(datasheet).Cells(SegRow + i, TrainCol1) + 1
          End If
          
          'Railroad crossing
          If Sheets(CrashSheet).Cells(CrashRow, RailCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, RailCol1) = Sheets(datasheet).Cells(SegRow + i, RailCol1) + 1
          End If
          
          'Transit vehicle involved
          If Sheets(CrashSheet).Cells(CrashRow, TransitCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, TransitCol1) = Sheets(datasheet).Cells(SegRow + i, TransitCol1) + 1
          End If
          
          'Collision with fixed object
          If Sheets(CrashSheet).Cells(CrashRow, FixedObCol2) = "Y" Then
             Sheets(datasheet).Cells(SegRow + i, FixedObCol1) = Sheets(datasheet).Cells(SegRow + i, FixedObCol1) + 1
          End If
          
          'Work Zone with workers present
          'If Sheets(CrashSheet).Cells(CrashRow, WZworkerCol2) = "Y" Then
          '    Sheets(datasheet).Cells(SegRow + i, WZworkerCol1) = Sheets(datasheet).Cells(SegRow + i, WZworkerCol1) + 1
          'End If
    End If