Sample Data Maker

[Anne Troy]

I was only able to find one sample data maker in Excel at Daniel Klann's site (anybody seen him lately? I haven't!) It's here:
http://www.danielklann.com/excel/using_arrays_to_improve_performance.htm

It's not really what I'm looking for. I'm working on a project that could require many worksheets. Rather than store the data in a bunch of overloaded workbooks, I'd like to provide a workbook the user can open, and it'll create the data all by itself.

If someone knows of something similar, I'd appreciate a link to it. Otherwise, perhaps someone can take the attached file and code it for me.

My idea is that each letter in column C would be appended to the beginning of each value in column A, then again to each value in Column B to create fake names. Date1 and Date2 columns can just be copied down, increasing by one each row, and the same with Amount1, Amount2, and Leading. It'll come out to about 2300 records.

Of course, whoever does this can feel free to use the file as a sample for the KB. :D :bow:

[johnske]

.....If someone knows of something similar, I'd appreciate a link to it. Otherwise, perhaps someone can take the attached file and code it for me.

My idea is that each letter in column C would be appended to the beginning of each value in column A, then again to each value in Column B to create fake names. Date1 and Date2 columns can just be copied down, increasing by one each row, and the same with Amount1, Amount2, and Leading. It'll come out to about 2300 records.

Of course, whoever does this can feel free to use the file as a sample for the KB. :D :bow:That's too easy!!! :yes

I modified it to generate a random Capital letter for each name in cols A and B to look more like fake names (and another random letter for col C). Oh, and I put the status of the procedure as a msg in the status bar...

Have a look at the attachment and see wotcha think. This's the code...Option Explicit
Sub SampleData()
Dim RanNum As Long, N As Long
Application.ScreenUpdating = False
For N = 2 To 2401
GetNum1:
'N.B. A to Z = Chr(65) to Chr(90)
RanNum = Int((90 * Rnd) + 1)
If RanNum < 65 Then GoTo GetNum1
Range("A" & N) = Chr(RanNum) & Range("A" & N)
GetNum2:
RanNum = Int((90 * Rnd) + 1)
If RanNum < 65 Then GoTo GetNum2
Range("B" & N) = Chr(RanNum) & Range("B" & N)
GetNum3:
RanNum = Int((90 * Rnd) + 1)
If RanNum < 65 Then GoTo GetNum3
Range("C" & N) = Chr(RanNum)
With Range("D" & N + 1)
.NumberFormat = "mm dd yyyy"
.Value = Range("D" & N) + 1
End With
With Range("E" & N + 1)
.NumberFormat = "mm dd yyyy"
.Value = Range("E" & N) + 1
End With
With Range("F" & N + 1)
.NumberFormat = "0.00"
.Value = Range("F" & N) + 1
End With
With Range("G" & N + 1)
.NumberFormat = "0.00"
.Value = Range("G" & N) + 1
End With
With Range("H" & N + 1)
.NumberFormat = "00000"
.Value = Range("H" & N) + 1
End With
Application.StatusBar = "Row Number " & N & "/2400" & " Done"
Next N
'tidy up
Rows(2402).EntireRow.Clear
Cells.Columns.AutoFit
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

[Bob Phillips]

GetNum1:
'N.B. A to Z = Chr(65) to Chr(90)
RanNum = Int((90 * Rnd) + 1)
If RanNum < 65 Then GoTo GetNum1
Range("A" & N) = Chr(RanNum) & Range("A" & N)

Easier to use RandBetween and eliminate the loop


GetNum1:
i = i + 1
'N.B. A to Z = Chr(65) to Chr(90)
RanNum = randbetween(65, 90)
If RanNum < 65 Then GoTo GetNum1
Range("A" & N) = Chr(RanNum) & Range("A" & N)


My tests showed that the Rnd function needed to be called 4 times on average as against once for RandBetween.

You will need to set a reference to atpvbaen.xls in the VBIDE.

.

[Anne Troy]

Well. I just tried John's and (as usual with John's code) it works terrific (certainly works great for my purposes)! I sure hope you're gonna a SampleDataMaker to the KB! :)

Thanks!!

[johnske]

Hi Anne,

Well if that's OK for your purposes, there's one thing left to do - put a "Restore" sub for when users save the demo data (as in attached).

(N.B. xld's suggestion would probably be a bit faster, but for the purposes of this demo I don't think it really essential)

Regards,
John :)

[Anne Troy]

It'd make a GREAT addin... :D