Random number placement

Jonesycc

New Member
Joined
Oct 15, 2014
Messages
17
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
Do you mean that you want to keep the 3 numbers your code generates and simply mix their order in cell AD7 or what?
 
Upvote 0
Yes I want to keep the values and just mix up the order in AD7.
After you run your macro cell AD7 looks like: 123/456/789 (numbers separated by slashes) - is that correct?
 
Upvote 0
No it doesn't have any slashes, and it's a combination of number letters and special characters, Example 8w& .
 
Upvote 0
No it doesn't have any slashes, and it's a combination of number letters and special characters, Example 8w& .
Seems a bit different than you initial post described. Can you post a sample of what the output of your macro looks like - include the spectrum of possible variations it will produce? I'm going offline now, but will look at it when I return if you haven't gotten a solution.
 
Upvote 0
Hi Joe

Yes I can the first value generated is a random number between 1 and 9, the second value generated is two random letters between a and z excluding ilo, and the third value generates a random special character from ! and /. Then the last command displays each value in order in AD7. So the contents of AD7 would look something like this 4qa%, please find my code below. You will notice the [Range("ad7") = kNumber & tNumber & wNumber] I would like to replace this with a code that will randomly mix up the generated values.

Code:
Sub RandomNumber()

'-----------------------------------------------------------------------------
' This Macro generates 4 random characters 1 number, 2 Letters, 
' 1 special Character.
'-----------------------------------------------------------------------------


    Dim J As Integer
    Dim K As Integer
    Dim T As Integer
    Dim W As Integer
    Dim kTemp As Integer
    Dim tTemp As Integer
    Dim wTemp As Integer
    Dim kOK As Boolean
    Dim wOK As Boolean
    Dim tOK As Boolean
    Dim kNumber As String
    Dim tNumber As String
    Dim wNumber As String


'-----------------------------------------------------------------------------


Application.ScreenUpdating = False


    Range("ad4").Activate
    Randomize
'----------------------------------------------------------------------------- 


	For J = 1 To 1
        kNumber = ""
        tNumber = ""
        wNumber = ""
 
'-----------------------------------------------------------------------------
' This statement generates 1 random number.
       
		For K = 1 To 1
        		Do
                		kTemp = Int((97 - 48 + 1) * Rnd + 48)
                		Select Case kTemp
                    			Case 50 To 57
                        			kOK = True
                    			Case Else
                        			kOK = False
                		End Select
            				Loop Until kOK
            					kOK = False
            					kNumber = kNumber & Chr(kTemp)
        		Next K
 
'-----------------------------------------------------------------------------
' This statement generates 2 random Letters.
       
       		For T = 1 To 2
        		Do
                		tTemp = Int((122 - 48 + 1) * Rnd + 48)
                		Select Case tTemp
                    			Case 97 To 104
                        			tOK = True
                    			Case 106 To 107
                        			tOK = True
                    			Case 109 To 110
                        			tOK = True
                    			Case 112 To 122
                        			tOK = True
                    			Case Else
                        			tOK = False
                		End Select
            				Loop Until tOK
            					tOK = False
            					tNumber = tNumber & Chr(tTemp)
        		Next T


'-----------------------------------------------------------------------------
' This statement generates 1 random special characters.


        	For W = 1 To 1
        		Do
                		wTemp = Int((46 - 14 + 1) * Rnd + 14)
                		Select Case wTemp
                    			Case 33 To 38
                        			wOK = True
                    			Case 40 To 43
                        			wOK = True
                    			Case 45 To 47
                        			wOK = True
                    			Case Else
                        			wOK = False
                		End Select
            				Loop Until wOK
            					wOK = False
            					wNumber = wNumber & Chr(wTemp)
        		Next W


'-----------------------------------------------------------------------------
' This statement displays and combines all the random numbers.


		ActiveCell.Value = kNumber
        	ActiveCell.Offset(1, 0).Select
        	Range("ad7") = kNumber & tNumber & wNumber
    Next J


'-----------------------------------------------------------------------------


End Sub
 
Upvote 0
Hi Joe

Yes I can the first value generated is a random number between 1 and 9, the second value generated is two random letters between a and z excluding ilo, and the third value generates a random special character from ! and /. Then the last command displays each value in order in AD7. So the contents of AD7 would look something like this 4qa%, please find my code below. You will notice the [Range("ad7") = kNumber & tNumber & wNumber] I would like to replace this with a code that will randomly mix up the generated values.

Rich (BB code):
Sub RandomNumber()

'-----------------------------------------------------------------------------
' This Macro generates 4 random characters 1 number, 2 Letters, 
' 1 special Character.
'-----------------------------------------------------------------------------


    Dim J As Integer
    Dim K As Integer
    Dim T As Integer
    Dim W As Integer
    Dim kTemp As Integer
    Dim tTemp As Integer
    Dim wTemp As Integer
    Dim kOK As Boolean
    Dim wOK As Boolean
    Dim tOK As Boolean
    Dim kNumber As String
    Dim tNumber As String
    Dim wNumber As String


'-----------------------------------------------------------------------------


Application.ScreenUpdating = False


    Range("ad4").Activate
    Randomize
'----------------------------------------------------------------------------- 


    For J = 1 To 1
        kNumber = ""
        tNumber = ""
        wNumber = ""
 
'-----------------------------------------------------------------------------
' This statement generates 1 random number.
       
        For K = 1 To 1
                Do
                        kTemp = Int((97 - 48 + 1) * Rnd + 48)
                        Select Case kTemp
                                Case 50 To 57
                                    kOK = True
                                Case Else
                                    kOK = False
                        End Select
                            Loop Until kOK
                                kOK = False
                                kNumber = kNumber & Chr(kTemp)
                Next K
 
'-----------------------------------------------------------------------------
' This statement generates 2 random Letters.
       
               For T = 1 To 2
                Do
                        tTemp = Int((122 - 48 + 1) * Rnd + 48)
                        Select Case tTemp
                                Case 97 To 104
                                    tOK = True
                                Case 106 To 107
                                    tOK = True
                                Case 109 To 110
                                    tOK = True
                                Case 112 To 122
                                    tOK = True
                                Case Else
                                    tOK = False
                        End Select
                            Loop Until tOK
                                tOK = False
                                tNumber = tNumber & Chr(tTemp)
                Next T


'-----------------------------------------------------------------------------
' This statement generates 1 random special characters.


            For W = 1 To 1
                Do
                        wTemp = Int((46 - 14 + 1) * Rnd + 14)
                        Select Case wTemp
                                Case 33 To 38
                                    wOK = True
                                Case 40 To 43
                                    wOK = True
                                Case 45 To 47
                                    wOK = True
                                Case Else
                                    wOK = False
                        End Select
                            Loop Until wOK
                                wOK = False
                                wNumber = wNumber & Chr(wTemp)
                Next W


'-----------------------------------------------------------------------------
' This statement displays and combines all the random numbers.


        ActiveCell.Value = kNumber
            ActiveCell.Offset(1, 0).Select
            Range("ad7") = kNumber & tNumber & wNumber
    Next J


'-----------------------------------------------------------------------------


End Sub
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:

Rich (BB code):
' 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
 
Upvote 0
Hi Joe

This is awesome, that did the trick once I figured out my value names weren't updated, works like a champ. Thanks for all of your help, you sir are a rock star! :biggrin: now I just have to figure out how to mark this post as solved.

Thanks Dave
 
Upvote 0
Hi Joe

This is awesome, that did the trick once I figured out my value names weren't updated, works like a champ. Thanks for all of your help, you sir are a rock star! :biggrin: now I just have to figure out how to mark this post as solved.

Thanks Dave
You are welcome - thanks for the reply. As far as I know, there is no way to mark a thread as solved on this forum.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top