Public Sub FillSales()
Dim Product_Desc(10) As String
Dim Product_Class
Dim DayNames
Dim State_Code
Dim Price
Dim ws As Worksheet
Dim wb As Excel.Workbook
Dim p As Integer
Dim pd As String
Dim pc As String
Dim StartDate As Date
Dim wd As Integer
Dim SaleDate As Date
Dim sd As Long
Dim ed As Long
Dim d As Long
Dim i As Long
Dim qty As Integer
Dim pr As Double
Dim dn As String
Dim rg As Excel.Range
Set wb = Application.ActiveWorkbook
Set ws = wb.Worksheets(1)
StartDate = "01/01/2015" ' Start Date
' Fill Arrays
DayNames = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
Product_Desc(0) = "Wigit"
Product_Desc(1) = "Cubit"
Product_Desc(2) = "Wingit"
Product_Desc(3) = "Middlibit"
Product_Desc(4) = "Aborate"
Product_Desc(5) = "Nickit"
Product_Desc(6) = "Frigity"
Product_Desc(7) = "Ligamet"
Product_Desc(8) = "Marid"
Product_Desc(9) = "Brigilan"
Product_Class = Array("Hardware", "Measure Device", "Electronics", "White Goods", "Tools", "Fast Food", "Fruit", "Kitchen Ware", "Computers", "Clothes")
State_Code = Array("QLD", "NSW", "VIC", "SA", "NT", "WA", "ACT")
Price = Array(3.2, 2.3, 5.65, 10.5, 6.5, 8#, 12#, 15.5, 22.2, 12.3)
ws.Cells(1, 1) = "Date"
ws.Cells(1, 2) = "Day"
ws.Cells(1, 3) = "Product"
ws.Cells(1, 4) = "State"
ws.Cells(1, 5) = "Quantity"
ws.Cells(1, 6) = "Product Class"
ws.Cells(1, 7) = "Price"
ws.Cells(1, 8) = "Sale Amount"
'Format Headings
Set rg = ws.Range(ws.Cells(1, 1), ws.Cells(1, 8))
rg.Font.Bold = True
Set rg = Nothing
ws.Columns("A:A").NumberFormat = "dd/mmm/yyyy"
ws.Columns("G:H").Style = "Currency"
Randomize
For i = 2 To 10000 ' Lines to populate
p = Int((10 * Rnd)) ' Generate Random array index for Product & price
pd = Product_Desc(p)
pr = Price(p)
p = Int((10 * Rnd)) 'Generate Randon Product Class
pc = Product_Class(p)
'Generate Date within year
d = Int(365 * Rnd) + 1
SaleDate = StartDate + d
wd = Excel.WorksheetFunction.Weekday(SaleDate, 2) 'Day of Week Monday = 1
ws.Cells(i, 1) = SaleDate 'Date of Sale
ws.Cells(i, 2) = DayNames(wd - 1) 'Day Name
ws.Cells(i, 3) = pd 'Product Description
ws.Cells(i, 4) = State_Code(Int((7 * Rnd))) 'State Description
qty = Int((20 * Rnd) + 1) 'Randon Quantity
If wd <= 5 Then 'If not weekend increase sales
qty = Int(qty * 1.2)
End If
ws.Cells(i, 5) = qty
ws.Cells(i, 6) = pc 'Product Class
ws.Cells(i, 7) = pr 'Price
ws.Cells(i, 8) = pr * qty 'Sale Value
Next i
' Set Columns to fit Date
ws.Columns("A:H").Select
ws.Columns("A:H").EntireColumn.AutoFit
End Sub