12 Number Random Help

[trustdss]

Need help with the following. Not good with VB, please, nice and simple?

I have a 12 digit number that I enter into cell C5 manually. The 12 digits I use are made up (i.e. 000345627863).

I then utilize the Luhn formula to validate a checksum of the entire number utilizing the 1st digit (read right to left i.e. 3). This check sum must be equal to a number ending in zero for the sequence of numbers to be valid. I use the =MOD(K17,10) formula to do this, where K17 is the SUM(K4:K15)(The Luhn Formula values). It then converts the entire 12 digit number into HEX utilizing DEC2HEX.

I'd like to utilize the following:

RANDBETWEEN(000100000000,000999999999)

in cell C15 to randomly generate my number. I would like it to keep generating until it's number is validated through it's checksum at cell K17 and then stop. Then I would like it to paste that number into cell C5 for conversion into HEX.

I know the F9 refreshes the random number but can I do this utilizing a macro and a Form Pushbutton to complete the entire process (generate random number, stop on number with the checksum of 0, and then paste into cell C5)?

Once again not very good with with VB, so the simpler, the better. THANKS IN ADVANCE.

Rick

[Jay Petrulis]

Hi,

This is not a complete solution for you, but please check this UDF to verify that my Luhn Formula method is correct.

If it is, then we can proceed with the rest of your request.

Function CC_Check(CardNum) As String

Dim x As Integer

Dim y As Integer

Dim Counter As Integer

Dim TempTotal As Integer

Dim LuhnCheck As Integer

   

    CardNum = CStr(CardNum)

    For x = Len(CardNum) To 1 Step -1

        Counter = Counter + 1

        TempTotal = TempTotal + SumDigits(Mid(CardNum, x, 1) * (2 - Counter Mod 2))

    Next x



    LuhnCheck = TempTotal Mod 10

    

    If LuhnCheck Then

        CC_Check = "not a valid number"

    Else

        CC_Check = "Luhn formula check OK"

    End If



End Function



Private Function SumDigits(num As Integer) As Integer

Dim i As Integer

Dim SumTotal As Integer

    For i = 1 To Len(CStr(num))

        SumTotal = SumTotal + Mid(num, i, 1)

    Next i

SumDigits = SumTotal

End Function

You would place this in a regular module and call the function as you would a native Excel function

=CC_Check(A1)

for instance.

This is returning a text string, but that can easily be amended to handle what you are looking to do.

_________________
Bye,
Jay

EDIT: The way I would imagine that the full solution would work would be to call this function from the routine, and loop until a legitimate entry was found. That might not be very efficient, but for a first stab it might do until refinement.

EDIT2: An even better solution would be to generate a randon 11-digit number and then append the check digit. I would imagine that would be quite easy to do and efficient to generate a nice sized sample.

[ This Message was edited by: Jay Petrulis on 2002-12-20 15:48 ]

[Jay Petrulis]

Hi,

The following macro generates 50 random 12-digit numbers (13 spaces to handle negatives) and places them in the next available row in column A. In column B is placed the DEC2HEX equivalent and in column C a check of the length of the number generated.

The first digit randomly selected is between -5 and +5 to minimize the chance that the number is outside of the support of the DEC2HEX function. There is still a chance to generate a #NUM! error, but it should be somewhat infrequent.

I have made both of the functions private here, but that is not critical.

Finally, you may want to format the first column as text so that any leading zero digits appear. Although they shouldn't affect the hex conversion, they will impact the column C results (which can be discarded anyway).

Option Explicit



Sub Generate_Numbers()

Dim n As Integer

Dim x As Integer

Dim y As Integer



Dim RandString As String

Dim Final_Number As String

Dim LastRow As Long



Randomize



'   initialize n as one less than your desired

'   number of digits

n = 11



For y = 1 To 50

    RandString = ""

    For x = 1 To n

        If x = 1 Then

            RandString = CStr(Int(Rnd * 10) - 5)

        Else

            RandString = RandString & CStr(Int(Rnd * 10))

        End If

    Next x

    

    Final_Number = CC_Check(RandString)

    

    With ActiveSheet

        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row

        .Cells(LastRow + 1, 1) = Final_Number

        .Cells(LastRow + 1, 2) = "=DEC2HEX(RC[-1])"

        .Cells(LastRow + 1, 3) = "=LEN(ABS(RC[-2]))"

    End With

Next y



End Sub



Private Function CC_Check(CardNum As String) As String

Dim x As Integer

Dim y As Integer

Dim Counter As Integer

Dim TempTotal As Integer

Dim LuhnCheck As Integer



   

    CardNum = CStr(CardNum)

    For x = Len(CardNum) To 1 Step -1

        Counter = Counter + 1

        

        If Mid(CardNum, x, 1) = "-" Then

            TempTotal = TempTotal

        Else

            TempTotal = TempTotal + SumDigits(Mid(CardNum, x, 1) * (2 - Counter Mod 2))

        End If

    Next x



    LuhnCheck = TempTotal Mod 10

    

    If LuhnCheck Then

        CC_Check = CardNum & CStr((10 - LuhnCheck))

    Else

        CC_Check = CardNum & "0"

    End If



End Function



Private Function SumDigits(num As Integer) As Integer

Dim i As Integer

Dim SumTotal As Integer

    For i = 1 To Len(CStr(num))

        SumTotal = SumTotal + Mid(num, i, 1)

    Next i

SumDigits = SumTotal

End Function

_________________
Bye,
Jay

EDIT: The following replacement of part of the code above should reduce the chance of a #NUM! conversion error.

    For x = 1 To n - 2

        If x = 1 Then

            RandString = CStr(Int(Rnd * 1100) - 550)

        Else

            RandString = RandString & CStr(Int(Rnd * 10))

        End If

    Next x

[ This Message was edited by: Jay Petrulis on 2002-12-20 18:32 ]

[tusharm]

On 2002-12-20 15:39, Jay Petrulis wrote:
Hi,

This is not a complete solution for you, but please check this UDF to verify that my Luhn Formula method is correct.
{snip}

EDIT2: An even better solution would be to generate a randon 11-digit number and then append the check digit. I would imagine that would be quite easy to do and efficient to generate a nice sized sample.

Looks good to me. I like the (2 - counter mod 2) as a toggle between 1 and 2.

Also, SumDigit as written is a general purpose method. However, in this context, its argument can never be larger than 18. So, it can be simplified to Nbr 10 + Nbr mod 10.

And, I couldn't agree more with Edit2. Generating 12 digit numbers and verifying if it satisfies Luhn's condition is incredibly wasteful. 90% of the numbers will have to be discarded -- I think.