Help Creating Promo Codes :)

[TripletDad]

I have about 15,000 promo codes to create and I want to do it using a Concatenate function...

I have a list of stores ("Stores" Tab, Column A) that I want to incorporate into the promo code -- so my formula would look like this:

=CONCATENATE('Stores'!A5,"-",RANDBETWEEN(100,999),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(65,90)),RANDBETWEEN(1000,9999))

Now for the challenge:

I need 50 codes for each store and I have a list of ~300 stores in column A

I need a formula (or need to modify the above formula) I can drag down 15,000 rows then copy/paste special the results...

Any ideas?

 

[Michael M]

Maybe something like this.
A couple of assumptions though
ALL the stores are listed in Col "A" of Sheet "Stores"
The promo codes are listed in consecutive columns for each store

Copy this code and then run it while you have a blank sheet active

Sub MM1()
Dim c As Integer, lr As Long, r As Long
lr = Sheets("Stores").Cells(Rows.Count, "A").End(xlUp).Row
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
    For c = 1 To 50
        For r = 1 To lr
            Cells(r, c).Formula = "=CONCATENATE(Stores!A" & r & ","" - "",RANDBETWEEN(100,999),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(65,90)),RANDBETWEEN(1000,9999))"
            With Cells(r, c)
                .Value = .Value
            End With
        Next r
    Next c
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[TripletDad]

Maybe something like this.
A couple of assumptions though
ALL the stores are listed in Col "A" of Sheet "Stores"
The promo codes are listed in consecutive columns for each store

Copy this code and then run it while you have a blank sheet active

Sub MM1()
Dim c As Integer, lr As Long, r As Long
lr = Sheets("Stores").Cells(Rows.Count, "A").End(xlUp).Row
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
    For c = 1 To 50
        For r = 1 To lr
            Cells(r, c).Formula = "=CONCATENATE(Stores!A" & r & ","" - "",RANDBETWEEN(100,999),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(65,90)),RANDBETWEEN(1000,9999))"
            With Cells(r, c)
                .Value = .Value
            End With
        Next r
    Next c
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Michael -

Thank you for putting this together... would you mind changing this so all the codes (for all stores) show in Column A (not consecutive columns in the new sheet)?

Thanks!

-Jeff

 

[Michael M]

Try

Sub MM1()
Dim c As Integer, lr As Long, r As Long
Sheets("Stores").Range("A1:A300").AutoFill Destination:=Sheets("Stores").Range("A1:A15000"), Type:=xlFillCopy
lr = Sheets("Stores").Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
        For r = 1 To lr
            Cells(r, 1).Formula = "=CONCATENATE(Stores!A" & r & ","" - "",RANDBETWEEN(100,999),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(65,90)),RANDBETWEEN(1000,9999))"
            With Cells(r, 1)
                .Value = .Value
            End With
        Next r
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Michael/Jeff. A few comments/suggestions regarding the suggested code (in post #4) for you to consider.

1. Maybe very unlikely, but what would stop multiple (or theoretically even all) the codes for a given store being identical?
Is it critical that all codes are unique?

2. The 'Stores' sheet data is modified by the code. That sheet may need returning to its original state.

3. The filling of rows is hard-code to 15,000 so why get the code to work out that lr = 15000?

4. If this method is persisted with, there is no need to apply the formula and value to each of the 15,000 rows separately. This would do the lot for that section of code

With Range("A1:A15000")
  .Formula = "=CONCATENATE(Stores!A1 & "" - "",RANDBETWEEN(100,999),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(65,90)),RANDBETWEEN(1000,9999))"
  .Value = .Value
End With

 

[TripletDad]

Peter - thank you for those suggestions... I think they're very valid points.

* It is not Critical that all codes are unique, but it would be very helpful.
* I'm not worried about the Stores sheet being modified, but I was thinking the codes would be inserted into a new sheet.
* I'd rather not limit this to 15K rows, when we add stores in the future, I'd like to be able to leverage this same macro.

Would you mind helping and providing the entire macro? I'm not sure where to insert the modified code you provided.

Thanks!

-Jeff

 

[Michael M]

Jeff
My original code allowed for any number of rows, but this will also put it onto a new sheet called "Stores Modified"

Sub MM1()
Dim c As Integer, lr As Long, r As Long, ws As Worksheet, ws2 As Worksheet
Set ws = Sheets("Stores")
ws.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Stores Modified"
Set ws2 = Sheets("Stores Modified")
ws2.Range("A1:A300").AutoFill Destination:=ws2.Range("A1:A15000"), Type:=xlFillCopy
lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
        For r = 1 To lr
            With Cells(r, 1)
                .Formula = "=CONCATENATE(Stores!A" & r & ","" - "",RANDBETWEEN(100,999),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(65,90)),RANDBETWEEN(1000,9999))"
                .Value = .Value
            End With
        Next r
Application.ScreenUpdating = True
End Sub

 

[TripletDad]

Michael - I'm sorry, but I still can't get this to work...

When I run the macro, it adds the tab you expected (Stores Modified), but the codes aren't correct. It concatenates the store and codes (ex: 999 - 853XI4940), but it only works for the ~300 stores I have listed. After that, it shows the following: - 425TZ8034.

I need it to do the following:

1) Look at 'Stores' Cell A1 - this holds the store number
2) Concatenate the following: Store Number, Hyphen, 3 Random Numbers, 2 Random Letters, 4 random numbers. Example: 999-482LX5916 where 999 is the store number. (Note: No spaces)
3) Although the store number is only listed one time on 'Stores', I need 50 unique codes for each store, so this would have to be repeated 50 times before creating a new set of codes for the store number located in cell A2.

Does this make sense?

Please guys... I really need your help.

Thanks,

-Jeff

 

[Michael M]

Jeff
I don't have Excel at the moment, but try this....UNTESTED

Sub MM1()
Dim c As Integer, lr As Long, r As Long, ws As Worksheet, ws2 As Worksheet
Set ws = Sheets("Stores")
ws.Range("A1:A300").AutoFill Destination:=ws.Range("A1:A15000"), Type:=xlFillCopy
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Stores Modified"
Application.ScreenUpdating = False
        For r = 1 To lr
            With Cells(r, 1)
                .Formula = "=CONCATENATE(Stores!A" & r & ","" - "",RANDBETWEEN(100,999),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(65,90)),RANDBETWEEN(1000,9999))"
                .Value = .Value
            End With
        Next r
Application.ScreenUpdating = True
End Sub

 

[Michael M]

Ooops....missed the bit about no spaces

Sub MM1()
Dim c As Integer, lr As Long, r As Long, ws As Worksheet, ws2 As Worksheet
Set ws = Sheets("Stores")
ws.Range("A1:A300").AutoFill Destination:=ws.Range("A1:A15000"), Type:=xlFillCopy
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Stores Modified"
Application.ScreenUpdating = False
        For r = 1 To lr
            With Cells(r, 1)
                .Formula = "=CONCATENATE(Stores!A" & r & ",""-"",RANDBETWEEN(100,999),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(65,90)),RANDBETWEEN(1000,9999))"
                .Value = .Value
            End With
        Next r
Application.ScreenUpdating = True
End Sub