Trying to modify existing macro to select randomly, versus in alphabetical order...
Results 1 to 7 of 7

Thread: Trying to modify existing macro to select randomly, versus in alphabetical order...

  1. #1
    Join Date
    Apr 2001
    Location
    Manhattan
    Posts
    169

    Trying to modify existing macro to select randomly, versus in alphabetical order...

    Hello all!
    In the past, I've had great success with a macro the user Kpark had whipped up for me... long story short, I want to add a feature to the macro, if you will. Basically, we have a list of 26k leads, however they're only 2300 locations. I do not want to send to all 26k leads, but a selected matrix (if 20, send 5, and so on). However, I want these leads to be selected at random, versus simply being the first x alphabetically. I will post the existing macro below for reference, and I'd appreciate if anyone could chime in on it.

    So again, I'm hoping to have the macro find the amount of leads per location, then randomly select the desired amount as per the table in sheet 3. Sheet 3 just has 2 columns A- is amount of hits per location, and B- is amount of catalogs to send. I.e. if 1, send 1, if 2, send 1, if 3, send 2, if 4, send 2, etc. Here is a sample of the data: https://docs.google.com/spreadsheets...VDE/edit#gid=0

    Thanks!

    Sub kpark()
    Dim Msg As Integer
    Msg = MsgBox("Have you sorted the mailing list?", vbYesNo)
    If Msg = 7 Then
    MsgBox ("Please sort the list.")
    Exit Sub
    End If

    Application.ScreenUpdating = False
    Dim LR1 As Long, i1 As Long, i2 As Long, i4 As Long, count As Long, catNum As Long

    count = 1
    catNum = 1
    i2 = 3
    i4 = 2
    LR1 = Sheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row
    Sheets("Sheet1").Range("A2:H2").Copy Sheets("Sheet2").Range("A2")

    For i1 = 3 To LR1
    If Sheets("Sheet1").Range("D" & i1).Value = Sheets("Sheet1").Range("D" & i1 - 1).Value Then
    count = count + 1
    If Sheets("Sheet3").Range("B" & count + 1).Value > catNum Then
    catNum = catNum + 1
    Sheets("Sheet1").Range("A" & i1 & ":H" & i1).Copy Sheets("Sheet2").Range("A" & i2)
    i2 = i2 + 1
    Else
    Sheets("Sheet1").Range("A" & i1 & ":H" & i1).Copy Sheets("Sheet4").Range("A" & i4)
    i4 = i4 + 1
    End If
    Else
    count = 1
    catNum = 1
    Sheets("Sheet1").Range("A" & i1 & ":H" & i1).Copy Sheets("Sheet2").Range("A" & i2)
    i2 = i2 + 1
    End If

    Next i1

    Application.ScreenUpdating = True

    End Sub
    Optiplex 390, i5-2400, 8GB Ram, Win 7 Pro, Dell E6500 w/XP, Poweredge 840, T100

  2. #2
    Join Date
    Feb 2000
    Location
    Idaho Falls, Idaho, USA
    Posts
    17,695
    Question:

    What column(s) are you sorting the list on? I assume that Sheet1 is the list that needs to be sorted.

    Once I have this information, I can change the message box to simply ask if you want the list sorted, and, if yes, sort it and proceed with the macro, selecting a random amount of leads based on your table in Sheet3.

  3. #3
    Join Date
    Apr 2001
    Location
    Manhattan
    Posts
    169
    Quote Originally Posted by jdc2000 View Post
    Question:

    What column(s) are you sorting the list on? I assume that Sheet1 is the list that needs to be sorted.

    Once I have this information, I can change the message box to simply ask if you want the list sorted, and, if yes, sort it and proceed with the macro, selecting a random amount of leads based on your table in Sheet3.

    Thanks JDC! Yes, we're sorting on column D, or INST (institution name). I think you know what's up, however - Again, the macro gets us the amount of leads, but they're always the first few in the alphabetical order. I'd love to get random ones from across the selection.
    Optiplex 390, i5-2400, 8GB Ram, Win 7 Pro, Dell E6500 w/XP, Poweredge 840, T100

  4. #4
    Join Date
    Feb 2000
    Location
    Idaho Falls, Idaho, USA
    Posts
    17,695
    I have some of the modifications ready. I will need to download your sample data file to finish and test them. I assume that it is available to anyone. I cannot do this from my work computer, so that will have to wait until later tonight.

  5. #5
    Join Date
    Apr 2001
    Location
    Manhattan
    Posts
    169
    Quote Originally Posted by jdc2000 View Post
    I have some of the modifications ready. I will need to download your sample data file to finish and test them. I assume that it is available to anyone. I cannot do this from my work computer, so that will have to wait until later tonight.
    Thanks anyway, however I just realized that the names are not sorted alphabetically! Go figure.. haha.
    Optiplex 390, i5-2400, 8GB Ram, Win 7 Pro, Dell E6500 w/XP, Poweredge 840, T100

  6. #6
    Join Date
    Feb 2000
    Location
    Idaho Falls, Idaho, USA
    Posts
    17,695
    I not sure that has any bearing on the issue, if you just want a random selection of a specified number of records from each Location group. As long as the list can be sorted by Location, your idea should work. I may end up sorting the list by Location and then Name, which is not a problem.

  7. #7
    Join Date
    Feb 2000
    Location
    Idaho Falls, Idaho, USA
    Posts
    17,695
    The link you posted returns a 404 error. Zip a copy of the sample file and attach it here.

Thread Information

Users Browsing this Thread

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

Posting Permissions

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