Random number placement

[Jonesycc]

I have VBA code that generates 3 separate values, kNumber/tNumber/wNumber, then it displays the number in cell ad7 in order. What I'm looking for is some code the will randomly mixup the numbers, and display them in ad7, can anyone help me out on this one?

 

[Rick Rothstein]

One way to do that is to add a select case to the last bit (in bold above) that randomly generates a number between 1 and 6 to represent one of the 6 possible arrangements of kNumber, tNumber and wNumber like this:

' This statement displays and combines all the random numbers.


        ActiveCell.Value = kNumber
            ActiveCell.Offset(1, 0).Select
'Generate a random number between 1 & 6 to specify one of 6 possible arrangements of three things
        Select Case Int(6 * Rnd + 1)
            Case 1: Range("ad7") = kNumber & tNumber & wNumber
            Case 2: Range("ad7") = kNumber & wNumber & tNumber
            Case 3: Range("ad7") = tNumber & kNumber & wNumber
            Case 4: Range("ad7") = tNumber & wNumber & kNumber
            Case 5: Range("ad7") = wNumber & kNumber & tNumber
            Case 6: Range("ad7") = wNumber & tNumber & wNumber
        End Select

Shouldn't the prefix on one of the highlighted text be a "k" instead of a second "w"?

 

[JoeMo]

Shouldn't the prefix on one of the highlighted text be a "k" instead of a second "w"?

Thanks for catching that Rick. Yes, that highlighted line should read:

Case 6: Range("ad7") = wNumber & tNumber & kNumber

Since there are 6 possible arrangements, each "?Number" should appear in each position twice, and only twice.

 

[Rick Rothstein]

I think this somewhat shorted macro will do what you want...

[table="width: 500"]
[tr]
	[td]Sub RandomNumber()
  Dim Order As Variant, Perm As Variant, Parts(1 To 3) As String
  Const Letters As String = "abcdefghjkmnpqrstuvwxyz"
  Const Symbols As String = """!#$%&()*+-./"
  Order = Array("1 2 3", "1 3 2", "2 1 3", "2 3 1", "3 1 2", "3 2 1")
  Perm = Split(Order(Application.RandBetween(0, 5)))
  Parts(Perm(0)) = Application.RandBetween(1, 9)
  Parts(Perm(1)) = Mid(Letters, Application.RandBetween(1, Len(Letters)), 1) & _
                   Mid(Letters, Application.RandBetween(1, Len(Letters)), 1)
  Parts(Perm(2)) = Mid(Symbols, Application.RandBetween(1, Len(Symbols)), 1)
  Range("AD7") = Join(Parts, "")
End Sub[/td]
[/tr]
[/table]

 

[Jonesycc]

Hi Rick, thanks for the code much more efficient than mine, but to be honest for a VBA macro neophyte like me took a bit to wrap my head around it. You are using commands I didn't know existed lol. All the same thank you very much.