Create data using VBA

[gknath79]

Hello Excel Gurus,

I see this is one of the fantastic and active forum for all excel queries and issues. Thank you for the good work. I would like to get some help in populating some data using a VBA. Below are the details for the same. Any help would be appreciated.

060657-295X
DDMMYY-295X

I would like to generate different datas like the above example. The first two character should be the date (01 to 31) and the next 2 character should be the month (01 to 12) and the next character should be the year (between 50 to 99) and finally the numeric value 295 can be any numeric digits from [100 to 999] and the last character can be anything between [A to Z] in capital letter. Now I wanted to generate nearly few hundred data's in the above format and all data's should be unique. Also there should be flexibility of populating more if needed later. Like if there is a provision to provide some range for the data population, it would be great. I.e like how many records has to be populated ? and providing the value 100 will populate 100 unique records or 100000 will populate 100000 unique records.

 

[Marc L]

Hi !

As a starter :​

Sub Demo1()
            Randomize
            ActiveSheet.UsedRange.Clear
    With CreateObject("Scripting.Dictionary")
        Do
            S$ = Format$(18264 + Fix(Rnd * 18262), "ddmmyy-") & 100 + Fix(Rnd * 900) & Chr$(65 + Fix(Rnd * 26))
         If Not .Exists(S) Then
           .Item(S) = ""
            Cells(.Count, 1).Value = S
         End If
        Loop Until .Count = 20000
            .RemoveAll
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[gknath79]

@Marc L, thank you. If I wanted to populate around 7Lakh unique records for example, which value in the above code should be tuned?

 

[Marc L]

Within the Loop Until codeline …​

 

[gknath79]

I have tried to increase the loop by 600000 but not able to populate the data more than 16384. I have attached the workbook for your reference and also the error message which occurs during the execution.

Can you please guide, on what I have missed?

Run-time error '1004':
Application-defined or object-defined error

 

[Marc L]

As I can't guess on which codeline your issue occurs …

But try the post #2 new code I've just edited !​

 

[gknath79]

@Marc L, the new one helps. But is there a quick way to cross verify whether all those data's are unique? And how many maximum unique combinations can be made from the given data?

 

[Marc L]

If you just well read my procedure and VBA inner help …
… but if you really need to cross verify use a SUMIF formula
or a sort and a formula to compare each cell witin previous one.

Maybe a bit more than 427 million combinations …
​

 

[p45cal]

not able to populate the data more than 16384

16384 is the number of columns on an Excel sheet. The code in your workbook had this line:
If IsError(Application.Match(s, [A1].Resize(, r), 0)) Then

remove that red comma and it would probably work again.

To get 700k results would take some time. I did a few time trials and tweaked Marc's code so that it writes results only once to the sheet, instead of 700k times and managed to shave about one third of the time off:

Sub Demo2()
Randomize
myLimit = 700000
ReDim myResults(1 To myLimit, 1 To 1)
ActiveSheet.UsedRange.Clear
With CreateObject("Scripting.Dictionary")
  Do
  s$ = Format$(18264 + Fix(Rnd * 18262), "ddmmyy-") & 100 + Fix(Rnd * 900) & Chr$(65 + Fix(Rnd * 26))
  If Not .Exists(s) Then
  .Item(s) = ""
  myResults(.Count, 1) = s
  End If
  Loop Until .Count = myLimit
End With
Range("A1").Resize(myLimit).Value = myResults
End Sub

It takes about a minute.