+ Reply to Thread
Results 1 to 3 of 3

Why am I getting number repeats in a row from macro

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Why am I getting number repeats in a row from macro

    Can one of you guys take a look at my macro and tell me how to fix my problem..
    My problem is I am getting numbers that repeat themselves in a row. Like double (9's) or double (18's), and I shouldn't be getting them.
    
    Option Explicit 
     
     
    Function generateNumbers(pattern As String, lNum As Long, uNum As Long) As Variant 
        Dim i As Integer 
        Dim x() As Integer 
        Redim x(0 To Len(pattern) - 1) As Integer 
         
         
        For i = 1 To Len(pattern) 
            Select Case UCase(Mid(pattern, i, 1)) 
            Case "E" 
                Do 
                    x(i - 1) = WorksheetFunction.RandBetween(lNum, uNum) 
                Loop Until x(i - 1) Mod 2 = 0 
            Case "O" 
                Do 
                    x(i - 1) = WorksheetFunction.RandBetween(lNum, uNum) 
                Loop Until x(i - 1) Mod 2 = 1 
            End Select 
             
        Next i 
        generateNumbers = x 
         
    End Function 
     
    Sub test() 
        Dim i As Long 
        Dim lNum As Long 
        Dim hNum As Long 
        Dim numCombos As Long 
         
         
         'Gave some input messages here
        lNum = InputBox("What is the lower boundary?", "Lower bound", 1) 
        hNum = InputBox("What is the upper boundary?", "Upper bound", 53) 
        numCombos = InputBox("How many combinations to produce?", "Combinations", 100) 
         
         
        Application.ScreenUpdating = False 
        Range("A3:F" & Rows.Count).ClearContents 
        For i = 3 To numCombos + 2 
            Range("A" & i & ":F" & i).Value = _ 
            generateNumbers(Range("I4").Value, lNum, hNum) 
        Next i 
        Application.ScreenUpdating = True 
    End Sub

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2404
    Posts
    24,841

    Re: Why am I getting number repeats in a row from macro

    I am not getting any numbers that repeat in a row. However, I am getting many instances of numbers that repeat in the same column in adjacent rows (i.e., C4 and C5 have the same number). In one run I got 23 such occurrences. That is out of a total of 594 pairs of numbers, or an occurrence of 3.9%.

    Your default is to generate an even or odd number in the range 1-53, which is 26 possible even numbers or 27 possible odd numbers. Let's take odd numbers. There are 729 possible combinations of two odd numbers in that range. Of those combinations, 27 of them are duplicates. That is a rate of 3.7%.

    In other words, what you are seeing is exactly what is predicted by probability. To reduce the number of duplicates, use a higher upper limit.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    06-23-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    239

    Re: Why am I getting number repeats in a row from macro

    Thank you for you help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] SUMIFS only UNTIL the number repeats
    By augustus88 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-10-2014, 02:04 PM
  2. count the number of repeats
    By sweetness34 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2013, 03:59 PM
  3. Replies: 6
    Last Post: 12-09-2012, 09:00 PM
  4. [SOLVED] restricting column repeats to a certain number
    By hbangalore in forum Excel General
    Replies: 6
    Last Post: 03-26-2012, 07:10 PM
  5. Formulae for: 4 most repeats,4 least repeats in a series of numbers
    By Sedge in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2010, 04:56 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1