counting letters but not working in new 2007 excel

[melaniethomson]

hi i had this formula in the old excel and it was working fine, i have now upgraded to the latest version and have now found that this formula is not working , do i have to turn anything on, can somebody help with this
many thanks melanie



Private Sub Worksheet_Change(ByVal Target As Range)
Dim strChr As String
Dim lCount As Long
Dim lSum As Long
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A1:A4100")) Is Nothing Then
If Not IsNumeric(Target) Then
For lCount = 1 To Len(Target)
strChr = Mid(Target, lCount, 1)
Select Case UCase(strChr)
Case "A": lSum = lSum + 1
Case "B": lSum = lSum + 2
Case "C": lSum = lSum + (9 / 2)
Case "CH": lSum = lSum + 8
Case "D": lSum = lSum + 4
Case "E": lSum = lSum + 5
Case "F": lSum = lSum + 80
Case "G": lSum = lSum + 3
Case "H": lSum = lSum + 8
Case "I": lSum = lSum + 10
Case "J": lSum = lSum + 10
Case "K": lSum = lSum + 20
Case "L": lSum = lSum + 30
Case "M": lSum = lSum + 40
Case "N": lSum = lSum + 50
Case "O": lSum = lSum + 70
Case "P": lSum = lSum + 80
Case "Q": lSum = lSum + 100
Case "QU": lSum = lSum + 26
Case "R": lSum = lSum + 200
Case "S": lSum = lSum + 60
Case "T": lSum = lSum + 9
Case "SH": lSum = lSum + 300
Case "TH": lSum = lSum + 400
Case "U": lSum = lSum + 6
Case "V": lSum = lSum + 6
Case "W": lSum = lSum + 6
Case "X": lSum = lSum + 80
Case "Y": lSum = lSum + 10
Case "Z": lSum = lSum + 90
'Add the rest here
End Select
Next lCount
'Puts the total in the same row one column right
Target(1, 2) = lSum
End If
End If
End Sub

[melaniethomson]

all ok, have found problem and fixed it