1

I have written a code that puts random numbers in a dictionary in Excel VBA. The purpose is to divied a different number into 3 number groups. If my number is 1-8 it should dived this into 2 groups with 3 numbers and 1 groups with 2 numbers. First part find out how many full groups it create and second part fills up the rest. All these numbers should be in random order

Everytime when I execute the macro it creates the number in different order, but when I close the file and start it up and run the Macro it creates the same pattern as it were before I close. Example when I run the macro First: 1,2,3,1,2,3,1,2 Next: 2,1,3,2,1,3,2,1 Next: 3,2,1,3,2,1,1,2 Restart the file it creates exact same pattern: First: 1,2,3,1,2,3,1,2 Next: Etc

it works perfect except the same pattern all the time, Can some one help me out so it it always create a new pattern when i restart the file?

Dim objDic As Object
Dim lngCnt, lngCnt2, lngCnt3 As Long
Dim llngCnt, llngCnt2, llngcnt3 As Long
Dim lngTot, lngOut, lngNum As Long
Dim Numvalue As Double
Dim Maxnumber As Double
Dim minnumber As Long
Dim Dela As Integer
Dim alastrow As Long
Dim blastrow As Long
Dim list As ListObject
Dim ws, Omg2 As Worksheet
Dim Lorng As Range
Dim lcol As Long

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

Set objDic = CreateObject("scripting.dictionary")
minnumber = 1
Maxnumber = (lngTot - 1) / 3


Numvalue = Maxnumber
    If (Numvalue - Int(Numvalue)) = 0 Then
     Else
        Numvalue = Int(Numvalue) + 1
        [Maxnumber] = Numvalue
     End If

For lngCnt = 1 To 2
    For lngCnt2 = 1 To Maxnumber
        lngCnt3 = lngCnt3 + 1
        objDic.Add lngCnt2 & "|" & lngCnt, lngCnt3

    Next
Next


For lngOut = 2 To (lngCnt3 + 1)
    lngNum = Int(Rnd() * objDic.Count)
    Cells(lngOut, 3) = Application.Index(Split(objDic.Keys()(lngNum), "|"), 1)
    objDic.Remove objDic.Keys()(lngNum)

    Next

objDic.RemoveAll

'Second part
alastrow = Range("C" & Rows.Count).End(xlUp).Row + 1
blastrow = lngTot - (Range("C" & Rows.Count).End(xlUp).Row)


For lngCnt = 1 To 1
    For lngCnt2 = 1 To blastrow
        lngCnt3 = lngCnt3 + 1
        objDic.Add lngCnt2 & "|" & lngCnt, lngCnt3
        Debug.Print lngCnt3 & "." & lngCnt2 & " " & lngCnt
    Next
Next

For lngOut = alastrow To (lngCnt3 + 1)
    lngNum = Int(Rnd() * objDic.Count)
    Cells(lngOut, 3) = Application.Index(Split(objDic.Keys()(lngNum), "|"), 1)
    objDic.Remove objDic.Keys()(lngNum)
Next

objDic.RemoveAll
YowE3K
  • 23,852
  • 7
  • 26
  • 40
PercyN
  • 43
  • 4
  • 1
    What's the point of `For lngCnt = 1 To 1`? it will always have a 1 in the middle? Your rnd is also on the dictionary count, which will increment/decrement. Do you want combinations of 1,2,3 generated at random, or any number? Can you add to your post. – Nathan_Sav Sep 04 '17 at 18:37
  • 2
    [`Randomize`](https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/randomize-statement) – Mathieu Guindon Sep 04 '17 at 19:15
  • Thanks Mat´s Mug exactly what I looked for – PercyN Sep 04 '17 at 19:21

0 Answers0