Dear all,

I have the following serial numbers:

41078928128
41078928136
41078928144
41078928151
41078928169
41078928177
41078928185
41078928193
41078928201

The last digit is a check digit. How can I get the formula /algorithm
to get the check digit? So that if I have the serial numbers I can add
the check digit with it?

Thanks

see:

http://groups.google.com/group/micro...f d6324bfa384
--
Gary''s Student - gsnu200793


"Abdul" wrote:

Dear all,

I have the following serial numbers:

41078928128
41078928136
41078928144
41078928151
41078928169
41078928177
41078928185
41078928193
41078928201

The last digit is a check digit. How can I get the formula /algorithm
to get the check digit? So that if I have the serial numbers I can add
the check digit with it?

Thanks

hi
assuming all of your serial numbers are the same length and start in A1,
this formula will get your check digit
=right(A1,1)
but i don't understand the rest of your question.


Regards
FSt1

"Abdul" wrote:

Dear all,

I have the following serial numbers:

41078928128
41078928136
41078928144
41078928151
41078928169
41078928177
41078928185
41078928193
41078928201

The last digit is a check digit. How can I get the formula /algorithm
to get the check digit? So that if I have the serial numbers I can add
the check digit with it?

Thanks

There are lots of different check bit algorithms. It look like you values
meet the Luhn algorithm. I did not check the code below that I got from
en.wikipedia.org. You can get more info from Wikpedia.

Public Function ValidateCardNumber(strCardNumber) As Boolean ' MOD 10 check
digit. "Luhn algorithm"
On Error GoTo Err

Dim intLoop As Integer, intSum As Integer
Dim bIsAlternate As Boolean, intProduct As Integer

For intLoop = Len(strCardNumber) To 1 Step -1

If bIsAlternate = False Then
intSum = intSum + CInt(Mid(strCardNumber, intLoop, 1))
bIsAlternate = True
Else

intProduct = CInt(Mid(strCardNumber, intLoop, 1)) * 2

If Len(CStr(intProduct)) = 2 Then
intSum = intSum + CInt(Mid(intProduct, 1, 1)) +
CInt(Mid(intProduct, 2, 1))
Else
intSum = intSum + CInt(intProduct)
End If

bIsAlternate = False

End If

Next intLoop

If intSum Mod 10 = 0 Then
ValidateCardNumber = True
Else
ValidateCardNumber = False
End If

Exit Function

Err:
MsgBox "Error in ValidateCardNumber()" & vbCrLf & Err.Number &
Err.Description
End Function


"Abdul" wrote:

Dear all,

I have the following serial numbers:

41078928128
41078928136
41078928144
41078928151
41078928169
41078928177
41078928185
41078928193
41078928201

The last digit is a check digit. How can I get the formula /algorithm
to get the check digit? So that if I have the serial numbers I can add
the check digit with it?

Thanks

I think he wants to know how to calculate the 11th digit starting with 10
digit data.
--
Gary''s Student - gsnu200793


"FSt1" wrote:

hi
assuming all of your serial numbers are the same length and start in A1,
this formula will get your check digit
=right(A1,1)
but i don't understand the rest of your question.


Regards
FSt1

"Abdul" wrote:

Dear all,

I have the following serial numbers:

41078928128
41078928136
41078928144
41078928151
41078928169
41078928177
41078928185
41078928193
41078928201

The last digit is a check digit. How can I get the formula /algorithm
to get the check digit? So that if I have the serial numbers I can add
the check digit with it?

Thanks

Using Joel's algorithm function and a routine to loop through the 10
possible check digits, find the one that passes the algorithm and place it
in the adjacent column. Assuming your serial numbers are in column A,
starting at row 1, this will place the registration number in column B. The
actual starting point can be adjusted to fit in the code.

Sub AddCheckNum()
Dim LRow As Long, ctr As Integer
Dim TestNum As String
Dim rng As Range, c As Range

LRow = Cells(Rows.Count, 1).End(xlUp).Row 'change col num to suit
Set rng = Range("A1:A" & LRow) 'change col & start row to suit
For Each c In rng
For ctr = 0 To 9
TestNum = c.Value & ctr
If ValidateCardNumber(TestNum) Then
c.Offset(0, 1).Value = TestNum
Exit For
End If
Next
Next
End Sub


Public Function ValidateCardNumber(strCardNumber) As Boolean ' MOD 10
checkdigit. "Luhn algorithm"
On Error GoTo Err

Dim intLoop As Integer, intSum As Integer
Dim bIsAlternate As Boolean, intProduct As Integer

For intLoop = Len(strCardNumber) To 1 Step -1

If bIsAlternate = False Then
intSum = intSum + CInt(Mid(strCardNumber, intLoop, 1))
bIsAlternate = True
Else

intProduct = CInt(Mid(strCardNumber, intLoop, 1)) * 2

If Len(CStr(intProduct)) = 2 Then
intSum = intSum + CInt(Mid(intProduct, 1, 1)) +
CInt(Mid(intProduct, 2, 1))
Else
intSum = intSum + CInt(intProduct)
End If

bIsAlternate = False

End If

Next intLoop

If intSum Mod 10 = 0 Then
ValidateCardNumber = True
Else
ValidateCardNumber = False
End If

Exit Function

Err:
MsgBox "Error in ValidateCardNumber()" & vbCrLf & Err.Number &
Err.Description
End Function

Check for wordwrap in the VBA editor after you paste this into a standard
code module.
Mike F

"Abdul" wrote in message
...
Dear all,

I have the following serial numbers:

41078928128
41078928136
41078928144
41078928151
41078928169
41078928177
41078928185
41078928193
41078928201

The last digit is a check digit. How can I get the formula /algorithm
to get the check digit? So that if I have the serial numbers I can add
the check digit with it?

Thanks

Thanks Mike and all other posters.

Great.
The initial testing shows i have got what I am looking for.

Thanks again

On Jun 22, 4:30*pm, "Mike Fogleman" wrote:
Using Joel's algorithm function and a routine to loop through the 10
possiblecheckdigits, find the one that passes the algorithm and place it
in the adjacent column. Assuming your serial numbers are in column A,
starting at row 1, this will place the registration number in column B. The
actual starting point can be adjusted to fit in the code.

Sub AddCheckNum()
Dim LRow As Long, ctr As Integer
Dim TestNum As String
Dim rng As Range, c As Range

LRow = Cells(Rows.Count, 1).End(xlUp).Row 'change col num to suit
Set rng = Range("A1:A" & LRow) 'change col & start row to suit
* * For Each c In rng
* * * * For ctr = 0 To 9
* * * * * * TestNum = c.Value & ctr
* * * * * * If ValidateCardNumber(TestNum) Then
* * * * * * * * c.Offset(0, 1).Value = TestNum
* * * * * * * * Exit For
* * * * * * End If
* * * * Next
* * Next
End Sub

Public Function ValidateCardNumber(strCardNumber) As Boolean ' MOD 10
checkdigit. "Luhn algorithm"
* * On Error GoTo Err

* * Dim intLoop As Integer, intSum As Integer
* * Dim bIsAlternate As Boolean, intProduct As Integer

* * For intLoop = Len(strCardNumber) To 1 Step -1

* * * * If bIsAlternate = False Then
* * * * * * intSum = intSum + CInt(Mid(strCardNumber, intLoop, 1))
* * * * * * bIsAlternate = True
* * * * Else

* * * * * * intProduct = CInt(Mid(strCardNumber, intLoop, 1)) * 2

* * * * * * If Len(CStr(intProduct)) = 2 Then
* * * * * * * * intSum = intSum + CInt(Mid(intProduct, 1, 1)) +
CInt(Mid(intProduct, 2, 1))
* * * * * * Else
* * * * * * * * intSum = intSum + CInt(intProduct)
* * * * * * End If

* * * * * * bIsAlternate = False

* * * * End If

* * Next intLoop

* * If intSum Mod 10 = 0 Then
* * * * ValidateCardNumber = True
* * Else
* * * * ValidateCardNumber = False
* * End If

* * Exit Function

Err:
* * MsgBox "Error in ValidateCardNumber()" & vbCrLf & Err.Number &
Err.Description
End Function

Checkfor wordwrap in the VBA editor after you paste this into a standard
code module.
Mike F

"Abdul" wrote in message

...

Dear all,

I have the following serial numbers:

41078928128
41078928136
41078928144
41078928151
41078928169
41078928177
41078928185
41078928193
41078928201

The lastdigitis acheckdigit. How can I get the formula /algorithm
to get thecheckdigit? So that if I have the serial numbers I can add
thecheckdigitwith it?

Thanks

However, for Excel:
=MOD(789-1,9)+1
=6

I believe, for Excel's MOD function, you need to use the following instead
of the above?

=MOD(MOD(B1-1,9)+1,9)

Without the second MOD function, numbers like 0, 891, 333, etc. (that is,
numbers whose digits total 9) will return 9 instead of what I think should
be 0. If you do this in VBA, then the second MOD function call should not be
necessary.

Rick

If we wish to get rid of the IF statement, one idea...

Function GenerateCheckDigit(n) As Long
Dim Fg As Boolean 'flag
Dim P As Long
Dim T As Long

'// Odd positions values are doubled
Fg = Len(n) Mod 2 = 1 'Odd?
For P = 1 To Len(n)
T = T + (((1 - Fg) * (Mid(n, P, 1)) - 1) Mod 9) + 1
Fg = Not Fg
Next P
GenerateCheckDigit = (10 - (T Mod 10)) Mod 10
End Function

--
Dana DeLouis