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

You could put all your logic in one big horrible formula, in L5, then copy down:

Formula:

=((CODE(RIGHT($D5,1))-64)*VALUE(RIGHT($E5,LEN($E5)-1)))+(MIN(INT($F5/12000)+1,5)*3)+((6-MATCH($G5,$N$17:$N$21,0))*3)+(MATCH($H5,$N$23:$N$24,0)*3)+((6-MATCH($I5,$N$26:$N$30,0))*5)+(MIN(INT(($J5-1)/10)+1,5)*5)+(LOOKUP($K5,{0,41,61,81,91},{5,4,3,2,1})*5)

Edit: hello, fellow Darlingtonite!

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.

Okay, the calculation for the Defect Extent score is based on the right character of the code. The score increases as the letter 'increases', so we can calculate a score from that letter. In the first version, we take the ASCII code of the right character - for example, the ASCII code for the letter "E" is 69. We wanted that to be 5, so we subtracted 64 from that code:

Formula:

=(CODE(RIGHT($D5,1))-64)

Now, we want E to score 3, so we could simply change the number subtracted from the ASCII code, to 66, so that E scores 3:

Formula:

=(CODE(RIGHT($D5,1))-66)

But now - the letter A has an ASCII code of 65, so this revised formula would return -1 for code SA. So we need to modify slightly, so that the score returned is never lower than 0:

Formula:

=MAX((CODE(RIGHT($D5,1))-66),0)

This is the corrected formula for the change to your Defect Extent scoring.



The UDF version of this would then become:

Function DefectExtentScore(DefectExtent As String)
    DefectExtentScore = WorksheetFunction.Max(Asc(Right(DefectExtent, 1)) - 66, 0)
End Function

An alternative approach, in the UDF, which may make it simpler for you to adjust the logic / scores, would be to explicitly state each Defect Extent value, and assign a score manually:

Function DefectExtentScore(DefectExtent As String)
    Select Case DefectExtent
        Case "SE":  DefectExtentScore = 3
        Case "SD":  DefectExtentScore = 2
        Case "SC":  DefectExtentScore = 1
        Case "SB":  DefectExtentScore = 0
        Case "SA":  DefectExtentScore = 0
        Case Else:  DefectExtentScore = 0
    End Select
End Function

I hope you followed all that!! Shout if you need further help with it.

Have a look at the attached file.

This has all the code in a module (Module1). You can access this by pressing Alt-F11, to open the VBE (Visual Basic Editor), then doubleclick on Module1 in the project explorer pane on the left hand side. This is where you can edit the code, so if you wanted to make changes to the rules for each score type, you would do it here.

Then in column L of the worksheet, we use the UDF (user defined function) as the formula:

Formula:

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

The big benefit of managing complex calculations in this way, is that when the logic changes (Defect Extent value scores, for example!), you only have to change the logic in one place, then all the formulae will automatically update based on the revised logic. You don't have to manually change any of the worksheet formulae, so you eliminate the risk of forgetting to update some / making errors in the formula update. I also think laying the logic out in this manner makes it much easier to understand your complex business rules.




(I have added some formulae to generate some random data in columns D:K, for testing. You can just ignore this.)