Modulus 10 question (sum strings, don't carry 10)

[mwheads]

Hi All

We have to produce a Hash total at the end of a banking export that sums strings but not carry 10 on any digits.

i.e.
+444444 (A1)
+190807 (A2)

=534241 (Result A3)

I need to create a VBA function that can run down the column, using a NEXT function.

The strings are actually all 32 characters long.

Any ideas

thanks

Paul

 

[PHV]

As a starting point, you may create an UDF in a standard code module:

Public Function myHash(r As Range)
Dim c, a, t
Dim i As Long
t = Split(StrConv(String(32, "0"), vbUnicode), Chr(0))
For Each c In r
  a = Split(StrConv(Format(Val(c.Text), String(32, "0")), vbUnicode), Chr(0))
  For i = 31 To 0 Step -1
    t(i) = (Val(t(i)) + Val(a(i))) Mod 10
  Next i
Next
myHash = Join(t, "")
End Function

And then, in A3, you may enter this formula:
=myHash(A1:A2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886