Generating Product Numbers

Hi,

Is it possible to generate a 8character code from the inputs in other fields, I'd really like to be able to do something like the following:

A1 - Jims Field
D1 - 1 (category)

B1 code:

[A1 CODE-FIRST 3 LETTERS][RANDOM CHARACTERS BETWEEN A-Z AND 0-9][D1 CAT ID]

Can this be done? I have no idea where to start.

Originally Posted by Luigi

Hi,

Is it possible to generate a 8character code from the inputs in other fields, I'd really like to be able to do something like the following:

A1 - Jims Field
D1 - 1 (category)

B1 code:

[A1 CODE-FIRST 3 LETTERS][RANDOM CHARACTERS BETWEEN A-Z AND 0-9][D1 CAT ID]

Can this be done? I have no idea where to start.

Hello Luigi,

Yes what you want can be done and it is not to difficult to do.
I am assuming you want the first three characters of Cell A1

PrefixStr = Left(Cells(1, 1).Value, 3)

Next, to Generate the Random Letters A-Z and Numbers 0-9
Call this Function. Each Call returns one Random Alpha-Numeric.
Paste this code in a VBA .bas Module
___________________________________________________

Private Function RndAlphaNumeric() As String

'GENERATE RANDOM NUMBERS FORM 1 - 36 AND CONVERT
'THEM INTO STRING VALUES OF A-Z, 0-9

Dim RndStr As String
Dim RndNum As Integer

Randomize 'Initialize the Random Number Generator

RndNum = Int((36 * Rnd) + 1)

'Convert to Number?
If RndNum >= 0 And RndNum <= 9 Then
RndStr = Chr$(48 + RndNum)
RndAlphaNumeric = RndStr
Exit Function
End If

'Convert to A - Z?
If RndNum >= 10 And RndNum <= 36 Then
RndStr = Chr$(55 + RndNum)
'Remove Comment on next line if you want lower case
'RndStr = Chr$(87 + RndNum)
RndAlphaNumeric = RndStr
Exit Function
End If

End Function
_________________________________________________________

Finally, concatenate PrefixStr & RndStr to form your code.

Any questions or problems,please contact me via email.

Leith Ross