Generating vailid check digits

[Mr. E.]

working on project to generate numbers that contain valid check digit
based on the following:

user types a number in to a cell (right most digit is check digit and
is labeled position 1)

the check digit is generated this way:

Step (1) starting from position 2 (and working leftward), add the
values in the even numbered positions

Step (2) Multiply the result of step 1 by 3

Step (3) Starting from position 3, add up the values in the
odd-numbered positions, skipping position 1 because it's the location
of the check digit

Step (4) Add up the results for steps 2 and 3

Step (5) The check digit is the smallest number which, added to the
result in Step 4, gives a multiple of 10

Now, my problem is that I want to be able generate a list of 10 valid
numbers based on what the user inputs into a cell..

For example, user inputs "65432105"
(5 is the check digit: ((6+4+2+0)*3) + (5+3+1)) = 45 ; 45 + 5 = 50
which is mult of 10, so 5 is valid check digit)

I need to list 10 other sequential numbers with valid check digits:

example: 65432112, 65432129,...,65432204

Also the user can input any size number (I don't need to veriy the
check digit of the input number)

any help appreciated.

 

[Tom Ogilvy]

Sub Make10()
sStr = ActiveCell.Text
sStr1 = Left(sStr,len(sStr)-1)
lnum = clng(sStr1)
for i = 1 to 10
sVal(i) = lNum + i
sVal(i) = GenCheck(sVal(i))
ActiveCell(i+1).Value = sVal(i)
Next
End Sub

Public Function GenCheck(sStr As String)
Dim esum As Long, osum As Long, cnum As Long
Dim i As Long
i = 0
For j = Len(sStr) To 1 Step -1
i = i + 1
If (i + 1) Mod 2 = 0 Then
esum = esum + CLng(Mid(sStr, i, 1))
Else
osum = osum + CLng(Mid(sStr, i, 1))
End If
Next
osum = esum * 3 + osum
cnum = Application.RoundUp(osum / 10, 0) * 10 - osum
GenCheck = sStr & cnum
End Function

produced:

65432112
65432129
65432136
65432143
65432150
65432167
65432174
65432181
65432198
65432204


Hopefully those are the right check digits. They match the ones you
presented.

 

[Mr. E.]

Thanks Tom!
very nice touch there in figuring out the next multiple of ten
(would've never thought of that)

Anyways, here's what I finally wound up using (wanted it to generate
100 valid numbers, and I needed to use Text Strings since the numbers
being inputed were too large for Excel)... I left your GenCheck
function alone since it worked perfectly! thanks again.

Dim sVal(100) As String

sStr = ActiveCell.Text
intLength = Len(sStr)

sStr = Replace(sStr, " ", "", 1) 'remove spaces from text
intLength = Len(sStr) 'length of text after
' spaces removed

sStr1 = Mid(sStr, (intLength - 3), 3) 'last three digits,
' without check digit
sStr2 = Mid(sStr, 1, (intLength - 4)) 'the rest of the digits
lNum = CLng(sStr1) 'convert the string into
' a number

'ready to generate 100 new numbers
For i = 1 To 100
sVal(i) = lNum + i 'new ending for the number

sVal(i) = GenCheck(sStr2 & sVal(i)) 'generate valid check
' digit for the number
ActiveCell(i + 1).Value = sVal(i)
Next

----
Public Function GenCheck(sStr As String)
'This function calculates the correct check digit for a given number,
'then returns that number with the check digit appended to it.

'(code from help on Excel newsgroup
Dim esum As Long, osum As Long, cnum As Long
Dim i As Long
i = 0
For j = Len(sStr) To 1 Step -1
i = i + 1
If (i + 1) Mod 2 = 0 Then
esum = esum + CLng(Mid(sStr, i, 1))
Else
osum = osum + CLng(Mid(sStr, i, 1))
End If
Next
osum = esum * 3 + osum
cnum = Application.RoundUp(osum / 10, 0) * 10 - osum
GenCheck = sStr & cnum
End Function