|
|
|
|
|
|
Excel
|
Sample Data Maker
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000
|
Submitted by:
|
johnske
|
Description:
|
On running the code twelve hundred rows of fake data is generated. (A progress bar shows the completed number of rows of data)
|
Discussion:
|
You may want to demonstrate how data can be collated/generated with VBA coding
|
Code:
|
instructions for use
|
Option Explicit
Sub UndoIt()
Sheet2.Cells.Copy Destination:=Sheet1.Cells
End Sub
Sub SampleData()
UndoIt
UserForm1.Show
End Sub
Option Explicit
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True
End Sub
Private Sub UserForm_Activate()
Dim RanNum As Long, N As Long
With UserForm1
.Width = 270
.Height = 72
End With
With TextBox1
.Width = 240
.Height = 14
.Top = 29
.Left = 10
.Width = 244
End With
With Label1
.Width = 1
.Height = 9
.Top = 31.5
.Left = 12
.BackColor = &HFF&
End With
With Label2
.Width = 155
.Height = 14
.Top = 8
.Left = 9
.Font = "Verdana"
.Font.Size = 10
End With
DoEvents
Application.ScreenUpdating = False
For N = 2 To 1201
Label1.Width = N / 5
Label2.Caption = "Progress: " & (100 * N / 1201) \ 1 & "%"
UserForm1.Caption = "Processing row " & N - 1 & " of 1200"
DoEvents
Randomize
GetNum1:
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
Next N
Unload Me
Rows(1202).EntireRow.Clear
Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
|
How to use:
|
- Open an Excel workbook
- Select Tools/Macro/Visual Basic Editor
- In the VBE window, select Insert/Userform (this will create Userform1)
- In the Toolbox shown, click 'textbox', now put the mouse pointer near the top left of the Userform and move the pointer down and to the right to create TextBox1
- From the Toolbox, click 'label', now put the mouse pointer near the top left of the Userform and move the pointer down and to the right to create Label1
- Repeat to create Label2 (need 2 labels)
- Double-click the userform and a code pane will appear
- Delete any code in this code pane and copy and paste the code for Userform1 from above
- Select Insert/Module, copy and paste the code for Module1 from above into it
- Now select File/Close and Return To Microsoft Excel
- Save your work
|
Test the code:
|
- It's easiest to download the zip attachment, unzip and open the workbook SampleDataMaker
- You can run the code in this demonstration workbook.
- Alternatively, copy Sheet 1 and paste this into the workbook you've coded (or else create your own base data)
|
Sample File:
|
SampleDataMaker.zip 43.82KB
|
Approved by mdmackillop
|
This entry has been viewed 468 times.
|
|