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.

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.

--
Regards,
Tom Ogilvy


"Mr. E." wrote in message
om...
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.

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