Replace letter without changing total

[sparky3883]

Hi All

I was wonering if anyone out there could please help me with som
coding trouble that i seem to be having.

I have created a sheet in Excel, that i am going to be using on a dail
basis.
The very bottom row, Row34, is a TOTAL row.
I have added some coding that, when the user types in a specifi
letter, a number, (which is represented to that letter) is added int
the TOTAL row for the appropriate column that it is in.
For example:
T = 1
s = 1
L = 4 and
K = 7

so, if i were to put the letters K, L and s into column B, the numbe
12 is added to B34. (the coding adds each number that is added to th
total row).
If i need to remove a letter from column B, for example the letter L,
is deducted from the total.
All this works very well, BUT, if i put a letter in a cell and the
decide that another leter should be placed there instead, i can't jus
overwrite the previous letter, becasue if i do, the new letter
allocated number just gets added to the previous letters allocate
number.
Example: If i place the letter T into a cell, the number 1 is place
into row34 of the same column. If i overwrite the letter T and place a
s there instead, the total goes up to 2.
Is there any ammendment that i can make to my code that will enable m
to over write what i put into a cell without it adding the new total t
the previous total.

Here is the coding that i have already:

Option Explicit

' Keep track of values entered so that we can subtract their value fro
a columns' total in the
' event that the user removes the character from the cell correspondin
to each array element.
Private marySavedValues(1 To 33, 4 To 36) As String

Private Function GetCharValue(ByVal IndexRow As Long, ByVal IndexColum
As Long) As Long

Dim lngCharValue As Long

' Determine the value of the specified cell.
Select Case Me.Cells(IndexRow, IndexColumn).Value
Case "T", "s"
lngCharValue = 1
Case "L"
lngCharValue = 4
Case "K"
lngCharValue = 7
Case Is = vbNullString
' If the user has removed the cell value, then we wil
subtract that character's
' value.
lngCharValue = RemoveCharValue(IndexRow, IndexColumn)
Case Else
lngCharValue = 0
End Select

GetCharValue = lngCharValue

End Function

Private Function RemoveCharValue(ByVal IndexRow As Long, ByVa
IndexColumn As Long) As Long

Dim lngCharValue As Long

' Determine the value of the specified array element.
Select Case marySavedValues(IndexRow, IndexColumn)
Case "T", "s"
lngCharValue = 1
Case "L"
lngCharValue = 4
Case "K"
lngCharValue = 7
Case Else
lngCharValue = 0
End Select

RemoveCharValue = -lngCharValue

End Function

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngCharValue As Long

' If the change is occurring within the desired range, the
calculate the value of the
' cell that has been changed and add it to the column's total fo
the column that the
' cell exists in.
If (Target.Column >= 4 And Target.row <= 33) And _
(Target.Column <= 36 And Target.row <= 33) Then

lngCharValue = GetCharValue(Target.row, Target.Column)
Me.Cells(34, Target.Column).Value = Me.Cells(34
Target.Column).Value + lngCharValue

' Save the character to the array so that we can remove th
value if the user
' removes the character.
marySavedValues(Target.row, Target.Column) = Target.Value

End If

End Sub


Any help with this problem would be much appreciated

Many thanks in advanc

 

[Frank Kabel]

Hi
I would try the following:
- create a user defined function which accepts a range (of your
letters) and returns the sum for this range. As you already have all
conversion routines in place it should be quite easy to create a UDF
(doing this you don't need the event procedure anymore).
This won't replace the char values but IMHO it would be much more
stable.

To give you an idea the following shoud work (though not tested / and
not much error checking included):

Public Function sum_characters(rng as range)
Dim ret_value
Dim cell as range
for each cell in rng
if cell.value<>"" then
ret_value = ret_value + GetSingleCharValue(cell.value)
end if
next

sum_characters = ret_value
End Function

----------

Public Function GetSingleCharValue (char_value)
Dim lngCharValue As Long

' Determine the value of the specified cell.
Select Case char_value
Case "T", "s"
lngCharValue = 1
Case "L"
lngCharValue = 4
Case "K"
lngCharValue = 7
Case Is = vbNullString
' If the user has removed the cell value, then we will
subtract that character's
' value.
lngCharValue = RemoveCharValue(IndexRow, IndexColumn)
Case Else
lngCharValue = 0
End Select

GetSingleCharValue = lngCharValue
End function