Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ramdon Numbers and Information (97 SR-2)

    I have A QA guy that has to send random surveys once a month to our customers. He needs to select 20 out of our Excel list. We have created a page in the workbook that creates the random number. Each business has a unique number ( 1,2,3...). How do I get the users complete info. to pull over with the number? How do I get the code to stop duplicating numbers ( We are pulling 25 numbers to get 20 clean numbers). Our random number generator goes as follows. Where A30 is '2' and A31 is the last customer number.
    =ROUND((RAND()*(A31-A30)+A30),0)
    I want the number generated to got to another page in the workbook and grab the contact information and put it on the same page that the random numbers are generated. Now the QA guy has to go to the Customer Info. worksheet and cut, paste then mail-merge.
    <img src=/S/flee.gif border=0 alt=flee width=25 height=25> Your time and help are greatly appreciated.

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

    Re: Ramdon Numbers and Information (97 SR-2)

    What you are asking would be very difficult to do using Worksheet functions. I would use a VBA routine. Since you didn't describe your workbook in too much detail, the code below makes some assumptions.

    1- Your workbook contains a sheet named "Customers" that contains the customer information with one customer per row, and that column A contains no empty cells.

    2- Your workbook contains a second sheet named "MailList", and you want the rows for the 20 randomly chosen customers to be copied to rows 1 through 20 of this sheet in random order.

    If your workbook does not meet those assumptions you will need to modify the code or change your workbook. The following code should do what you want:

    <pre>Public Sub GenerateMailList()
    Dim iCustomers As Integer, iUsed(1 To 20) As Integer
    Dim I As Integer, J As Integer, iRand As Integer
    iCustomers = Worksheets("Customers").Range("A1").End(xlDown).Ro w
    Randomize
    I = 1
    Do While I <= 20
    iRand = Int((iCustomers - 1) * Rnd + 1)
    For J = 1 To 20
    If I = J Then
    iUsed(I) = iRand
    I = I + 1
    Exit For
    End If
    If iRand = iUsed(J) Then
    Exit For
    End If
    Next J
    Loop
    For I = 1 To 20
    Worksheets("Customers").Range("A1").Offset(iUsed(I ) - 1, 0).EntireRow.Copy
    Worksheets("MailList").Range("A1").Offset(I - 1, 0).PasteSpecial Paste:=xlPasteValues
    Next I
    Application.CutCopyMode = False
    End Sub
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ramdon Numbers and Information (97 SR-2)

    I have attached the file with fictional info.
    Attached Files Attached Files

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

    Re: Ramdon Numbers and Information (97 SR-2)

    The workbook you sent had VBE password protected, so I could not put the macro into your work book and test it. I created a new workbook and copied the contacts sheet to it. I then created a new "Selection" sheet and put my macro into the workbook. Pressing the "New Mail List" button on the bottom on the "Selection" sheet should generate a new random list of contacts on that sheet.
    Attached Files Attached Files
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ramdon Numbers and Information (97 SR-2)

    This is great! I copied the macro over to my actual workbook. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> Bad news. I am required to open the "Sample workbook" in order to run the Macro. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Also when I attempt to run a Mail merge, it runs all of the customers not just those from "Selection". <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> I am sure it is me, and you have taken me 1000 paces forward so again, thanks! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

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

    Re: Ramdon Numbers and Information (97 SR-2)

    If you copied the button from my workbook to your workbook, the button would reference the macro in my workbook. If you copied the macro from my workbook and pasted it into your's, then you will either need to create a new button or change the one you copied to reference the macro in your workbook.

    I don't know how you are doing the mail merge, so I can't guess what might be happening there.
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ramdon Numbers and Information (97 SR-2)

    FYI: there is also the Sampling tool in the Analysis ToolPak (Tools >> Data Analysis >> Sampling) that allows you to randomly select a fixed number of items from a data input range. But, Legares macro is the preferred option, because the sampling is with replacement (that is, each time an item is selected, it is placed back before the next item is selected, which has as a consequence that the same item may appear more than once in the sampling list). I am not quite sure that this is done on purpose, it might be a bug in the Analysis Toolpak.

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ramdon Numbers and Information (97 SR-2)

    The redoing the button worked. It's amazing how the little things bite you. <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ramdon Numbers and Information (97 SR-2)

    The "real" problem here is that there's no such thing as "ramdon" numbers. I guess that's a random spelling!! Maybe random numbers would not have any problem here! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

Posting Permissions

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