-
June 6th, 2016, 12:19 PM
#1
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
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
-
Forum Rules
|
|