Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting a Text Box (XP)

    I am playing around with the generation of random numbers to simulate lottery results. I have some success but I have two problems.

    1#
    The 6 random numbers populate a text box successfully, but I want them to be sorted in ascending order. Is this possible.

    2#
    Simultaneously, they are sent to Range A1 to A6 on a worksheet then sorted. I have noticed that when 2 consecutive numbers are present in the text box , one of them is repeated on the worksheet.

    TIA
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting a Text Box (XP)

    Hi rob,

    1) You can generate the numbers in a worksheet (which may be hidden) and sort them using the Sort method of the Range object. Or you can generate them in an array, and use one of the many available sorting routines to sort the array. Then pull the numbers from the range or array into the text box. Post back if you want details.

    2) I'm sorry, I don't understand what's happening here. How do you send the numbers to A1:A6?

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    Where is the textbox? On a UserForm or on a Worksheet. If it is on a worksheet, is it from the Controls Toolbox or the Forms Toolbox? How did you put the numbers into the text box? In general, text in a text box is in the order you put it there, so if you sort them before you put them in the text box, they should be sorted.

    I don't understand question #2. If one of the numbers is repeated, is another number missing? How are the numbers "sent to Range A1 to A6?" Are these numbers being generated in VBA code? If so, how about showing us the code.
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    Thanks Hans and Legare for your responses. Here is the code I am using and I've included a screenshot exemplifying 2#.

    Option Explicit 'Force variable declaration
    Sub RandomNumbers()
    Dim TB As TextBox
    Dim Col As Collection 'The collection I will use to store the numbers
    Dim Ar(1 To 49) As Integer 'The array to store the values from the collection
    Dim i As Integer 'Counter for loops
    Dim X As Integer 'Variable to store the random generated number
    Randomize 'Just once to ensure that I get random values
    Set Col = New Collection 'Get the collection ready to use
    For i = 1 To 49 'The possible numbers that I can have as a result is all the numbers from 1 to 49 so
    Col.Add i 'add all the possible numbers to the collection
    Next
    For i = 1 To 6 'Now to get the 6 of the 49 numbers I added in the previous loop
    X = RandomInteger(1, Col.Count) 'Get a random item from the collection (that exists for sure)
    Ar(i) = Col.Item(X) 'Add it to the array
    Col.Remove X 'Remove it so I don't add it again
    Sheets("Sheet1").Range("A" & i) = X
    Next
    ShowInTextBox TB, Ar 'Just to print the data and see it
    SortIt
    End Sub

    Private Function RandomInteger(LoIrbound As Integer, Upperbound As Integer) As Integer 'The random number generator code
    RandomInteger = Int((Upperbound - LoIrbound + 1) * Rnd + LoIrbound)
    End Function

    Private Sub ShowInTextBox(TB As TextBox, A() As Integer) 'Just a sub to show the data in a textbox
    Dim i As Integer
    UserForm1.TB.Text = ""
    'For i = 1 To UBound(A)
    For i = 1 To 6
    UserForm1.TB.Text = UserForm1.TB.Text & CStr(A(i)) & " "
    'UserForm1.TB.Text = UserForm1.TB.Text & CStr(A(i)) & vbCrLf
    Next
    UserForm1.TB.Text = Left$(UserForm1.TB.Text, Len(UserForm1.TB.Text) - 2)
    End Sub

    Things are getting stranger. As you can see, the worksheet range no longer matches exactly the text box values.???
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    What you are putting into the range A1:A6 is the index into your collection, not the random numbers. Try changing this line:

    <code>
    Sheets("Sheet1").Range("A" & i) = X
    </code>

    to

    <code>
    Sheets("Sheet1").Range("A" & i) = Ar(i)
    </code>

    If your procedure SortIt sorts the array (you didn't show us that code so I can't tell), you are executing it AFTER you put the numbers into to text box. Sort the array before putting the values into the textbox.
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting a Text Box (XP)

    Problem is that by removing items from the collection, you are shifting the remaining items. Say that the first item you remove is 13. After that, the 13th item will contain 14, the 14th item will contain 15 etc.

    See for example Excel: Generate Unique Random Numbers Between 2 Specified Numbers.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    I don't think that is a problem with the way he is doing it. After removing the number from the collection, he uses the new collection count property as the upper bound for the next index into the collection. The problem is that he is putting the index into the cells, not the corresponding value from the collection.
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting a Text Box (XP)

    Yep, you're correct. Sorry about the confusion.

    For Rob, one other point: since you concatenate with a space instead of vbCrLf, you should change

    UserForm1.TB.Text = Left$(UserForm1.TB.Text, Len(UserForm1.TB.Text) - 2)

    to

    UserForm1.TB.Text = Left$(UserForm1.TB.Text, Len(UserForm1.TB.Text) - 1)

  9. #9
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    Thanks Legare. You were spot on with your analysis. The SortIt procedure only sorts the worksheet range once it has been populated, so that works fine.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  10. #10
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    < Or you can generate them in an array, and use one of the many available sorting routines to sort the array. Then pull the numbers from the range or array into the text box. Post back if you want details.>

    Yes please! I've created the array, as you have seen. How would I sort it prior to sending its elements to the test box.
    TIA.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting a Text Box (XP)

    Here is a simple bubble sort:

    Sub Sortit(a() As Integer)
    Dim i As Integer
    Dim j As Integer
    Dim intTemp As Integer
    For i = LBound(a) To UBound(a) - 1
    For j = i + 1 To UBound(a)
    If a(i) > a(j) Then
    intTemp = a(i)
    a(i) = a(j)
    a(j) = intTemp
    End If
    Next j
    Next i
    End Sub

    You'd have to modify the code like this:
    <code>
    For i = 1 To 6
    ...
    Next
    SortIt Ar
    ShowInTextBox TB, Ar
    </code>
    SortIt is called before ShowInTextBox, and the array is provided as argument.

  12. #12
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    Would something like this that I found surfing around help?

    Danny

  13. #13
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    Thanks for that, Danny. It's a very instructive little piece.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  14. #14
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    Thanks again, Hans. I'll have a little play.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  15. #15
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a Text Box (XP)

    Here is another take on the Lottery Generator I wrote many moons ago.

    It still has not found me my winning numbers!
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

Page 1 of 2 12 LastLast

Posting Permissions

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