No Macro Experience - Need to run a loop (I think?!) over a set of data for multiple rows

I would make it neater, by using some UDFs:

Function BridgeScore(DefectExtent As String, DefectSeverity As String, TrafficFlow As Long, _
                    RoadType As String, OverUnder As String, WaterproofingType As String, _
                    WaterproofingAge As Integer, StructureCondition As Single)
    Dim i As Integer, RunningTotal As Integer

    RunningTotal = DefectExtentScore(DefectExtent) * DefectSeverityScore(DefectSeverity)
    RunningTotal = RunningTotal + TrafficScore(TrafficFlow) * 3
    RunningTotal = RunningTotal + RoadTypeScore(RoadType) * 3
    RunningTotal = RunningTotal + OverUnderScore(OverUnder) * 3
    RunningTotal = RunningTotal + WaterproofingTypeScore(WaterproofingType) * 5
    RunningTotal = RunningTotal + WaterproofingAgeScore(WaterproofingAge) * 5
    RunningTotal = RunningTotal + StructureConditionScore(StructureCondition) * 5
    BridgeScore = RunningTotal
End Function


Function DefectExtentScore(DefectExtent As String)
    DefectExtentScore = Asc(Right(DefectExtent, 1)) - 64
End Function

Function DefectSeverityScore(DefectSeverity As String)
    DefectSeverityScore = WorksheetFunction.Min(CInt(Right(DefectSeverity, Len(DefectSeverity) - 1)), 4)
End Function

Function TrafficScore(TrafficFlow As Long)
    TrafficScore = WorksheetFunction.Min((TrafficFlow - 1) \ 12000 + 1, 5)
End Function

Function RoadTypeScore(RoadType As String)
    Select Case RoadType
        Case "Motorway":                    RoadTypeScore = 5
        Case "Trunk Road - Dual":           RoadTypeScore = 4
        Case "Trunk Road - Single":         RoadTypeScore = 3
        Case "County Road":                 RoadTypeScore = 2
        Case "Accommodation Bridge":        RoadTypeScore = 1
        Case Else:                          RoadTypeScore = 0
    End Select
End Function

Function OverUnderScore(OverUnder As String)
    Select Case OverUnder
        Case "Overbridge":                  OverUnderScore = 1
        Case "Underbridge":                 OverUnderScore = 2
        Case Else:                          OverUnderScore = 0
    End Select
End Function

Function WaterproofingTypeScore(WaterproofingType As String)
    Select Case WaterproofingType
        Case "None Present":                WaterproofingTypeScore = 5
        Case "Bitumen Emulsion or Unknown": WaterproofingTypeScore = 4
        Case "Mastic Asphalt":              WaterproofingTypeScore = 3
        Case "Bitumen Sheet":               WaterproofingTypeScore = 2
        Case "Sprayed/Liquid":              WaterproofingTypeScore = 1
        Case Else:                          WaterproofingTypeScore = 0
    End Select
End Function

Function WaterproofingAgeScore(WaterproofingAge As Integer)
    WaterproofingAgeScore = WorksheetFunction.Min((WaterproofingAge - 1) \ 10 + 1, 5)
End Function

Function StructureConditionScore(StructureCondition As Single)
    Select Case StructureCondition
        Case Is <= 40:  StructureConditionScore = 5
        Case 40 To 60:  StructureConditionScore = 4
        Case 60 To 80:  StructureConditionScore = 3
        Case 80 To 90:  StructureConditionScore = 2
        Case Is > 90:   StructureConditionScore = 1
        Case Else:      StructureConditionScore = 0
    End Select
End Function

Then in L5, you can enter:

Formula:

=BridgeScore(D5,E5,F5,G5,H5,I5,J5,K5)

and copy down. Much neater.