Hi,

Hoping some Excel guru can spread some devine light on a problem thats
quite knotty :)

I need to generate a list of numbers that will follow a pre-defined Modulus
11
routine. Characteristics of the number are :

8 Digits long,
Always begins with 5 and ends with a 1 or a 2.
If the number ends with a 2 then add five to the weighting calculation and
continue with Mod 11 to obtain the Check Digit.

Weightings in character positions are 5,3,8,2,9,1, CheckDigit here , 1 or 2.

Not sure how or if i would create a function for this !!

Any Ideas... Any Takers.....


TIA
Andy

If you could explain to the nonmathematicians among us what a modulus
sequence is and some explanation of the weighting and the check-digit
calculation, I can't imagine that coming up with a function would be too
difficult.

--

Vasant


"Stoke_A_GOaT" wrote in message
...
Hi,

Hoping some Excel guru can spread some devine light on a problem thats
quite knotty :)

I need to generate a list of numbers that will follow a pre-defined
Modulus
11
routine. Characteristics of the number are :

8 Digits long,
Always begins with 5 and ends with a 1 or a 2.
If the number ends with a 2 then add five to the weighting calculation and
continue with Mod 11 to obtain the Check Digit.

Weightings in character positions are 5,3,8,2,9,1, CheckDigit here , 1 or
2.

Not sure how or if i would create a function for this !!

Any Ideas... Any Takers.....


TIA
Andy

Andy

You are a bit short on detail on the mod 11 options for 0, 1 etc but this should give you the idea.

Sub aaa()
For i = 0 To 9
For j = 0 To 9
For k = 0 To 9
For l = 0 To 9
For m = 0 To 9
For n = 1 To 2
z = Val(5 & i & j & k & l & m & n)
ActiveCell.Value = makecheck(z)
ActiveCell.Offset(1, 0).Select
Next n
Next m
Next l
Next k
Next j
Next i
End Sub

Function makecheck(x)
tot = 0
arr = Array(5, 3, 8, 2, 9, 1)
For i = 1 To 6
tot = tot + Val(Mid(x, i, 1)) * arr(i - 1)
Next i

If Mid(x, 7, 1) = 2 Then tot = tot + 5

moder = tot Mod 11
chker = 11 - moder

makecheck = Val(Mid(x, 1, 6)) & chker & Val(Right(x, 1))


End Function

Tony

----- Stoke_A_GOaT wrote: -----

Hi,

Hoping some Excel guru can spread some devine light on a problem thats
quite knotty :)

I need to generate a list of numbers that will follow a pre-defined Modulus
11
routine. Characteristics of the number are :

8 Digits long,
Always begins with 5 and ends with a 1 or a 2.
If the number ends with a 2 then add five to the weighting calculation and
continue with Mod 11 to obtain the Check Digit.

Weightings in character positions are 5,3,8,2,9,1, CheckDigit here , 1 or 2.

Not sure how or if i would create a function for this !!

Any Ideas... Any Takers.....


TIA
Andy