Solved

# Random Data Generator

Posted on 2014-08-24
172 Views
EE Pros,

I have a great little macro written by EE that helps me declare a frequency of sensor data based on a specific time horizon.  The frequency is based on a unit of time.  What I need is a macro that reads the frequency and creates both a date entry and a random sensor reading for as many frequency events as declared.  The formula for the sensor reading is generated using "randbetween" formula with a declared Min. / Max.

Code attached.

B.
0
Question by:Bright01

LVL 35

Expert Comment

For your excel file, this is the code I came up with:

``````Sub random()

val_max = Range("C9").Value
val_min = Range("C10").Value
Val_Start = Range("C5").Value + Range("C6").Value
Val_End_Delta = Round(Range("D4").Value)

For i = 13 To (Val_End_Delta + 13) Step 1
Cells(i, 3).NumberFormat = "d-m-yyyy hh:mm"
Cells(i, 3).Value = Val_Start + i
Cells(i, 4).Value = Round(val_min + (val_max - val_min) * Rnd())
Next

End Sub
``````

It assumes cells are filled with values though, not with formulas. Please test first and see if it suits your needs?
0

Author Comment

Kimputer,

Thank you!  Very nice.  One problem.  If you shift the frequency (e.g. months).... the timing stays by day.  Can you take a quick look?

Thank you,

B.
0

LVL 35

Assisted Solution

The code doesn't include deletion of old data. Therefore if you SHORTEN the months, you won't notice any differences (except the random data of course). If you LENGTHEN the months, you will see dates are added.
I figured you'd start this file everytime you needed it, and save it as something else, so the original stays the same.
If you want me to adjust the code, it's also possible of course.

Let me know if this wasn't not the problem you saw.

Amended code for deleting table:

``````Sub random()

Application.ScreenUpdating = False

val_max = Range("C9").Value
val_min = Range("C10").Value
Val_Start = Range("C5").Value + Range("C6").Value
Val_End_Delta = Round(Range("D4").Value)

If Worksheets(1).UsedRange.Rows.Count > 13 Then
Range("C13:D" & Worksheets(1).UsedRange.Rows.Count).Delete
End If

For i = 13 To (Val_End_Delta + 13) Step 1
Cells(i, 3).NumberFormat = "d-m-yyyy hh:mm"
Cells(i, 3).Value = Val_Start + i
Cells(i, 4).Value = Round(val_min + (val_max - val_min) * Rnd())
Next

Application.ScreenUpdating = True

End Sub
``````
0

Author Comment

Yes.  Understand.  And I can write the clear contents macro.  The problem isn't that.  It's the fact that if you change the timeframe to months, the dates still progress by day; not by month.  When you change the value in C3, it should change the way the dates appear to reflect the choice (month, day, year, min, sec. etc.).  That's the issue.

B.
0

LVL 35

Expert Comment

Aha I see now, requires quite a bit of extra code. Patience please. But I'll be looking at C4 rather than C3 ?
0

Author Comment

Yes!

Thanks.
0

LVL 27

Accepted Solution

Hi.  I may be late in the game (didn't see kimputer's posts) but here's my version of your workbook with the following code:
``````Sub Generate_Samples()
Dim lngFreq, r As Long
Dim dtStart As Date
Dim dblTime As Double
Dim intMin, intMax As Integer

lngFreq = Round(Range("D4"), 0)
dtStart = Range("D5") + Range("D6")
intMin = Range("C9").Value
intMax = Range("C10").Value

Select Case Range("C4").Value
Case "Year(s)"
dblTime = 365.04
Case "Month(s)"
dblTime = 30.42
Case "Day(s)"
dblTime = 1
Case "Hour(s)"
dblTime = 1 / 24
Case "Minute(s)"
dblTime = 1 / 1440
Case "Second(s)"
dblTime = 1 / 86400
End Select

'Clear existing data
Range("C13:D" & Cells.SpecialCells(xlLastCell).Row).ClearContents

'apply random values
For r = 0 To lngFreq - 1
Cells(r + 13, 3).Value = dtStart + (dblTime * r)
Cells(r + 13, 4).Value = WorksheetFunction.RandBetween(intMin, intMax)
Next r
Msgbox ("Done.")
End Sub
``````

I flipped the Min and Max values; it seemed to make more sense visually.  The area of the code that you'll want to look at is the Select...Case section that determines the time increment.  This gets interesting with month and year increments, since your matrix has - IMO - unusual values for those.  However, I moved them into this code to be consistent.  You can always change these values if you have another preference.

Additionally, I took your instruction about Randbetween literally; that's the worksheet function I used.  If you need random fractional values between the min and max, line 33 will need to be changed.

Modified file attached.

Regards,
-Glenn
0

Author Closing Comment

Glenn and Kimputer,

This works great!  Exactly what I was looking for.  Thank both of you for putting in the effort on this.  Now I'm off to random generate some numbers.  Will be asking for a modification shortly.

Thanks again, Great Teaming!

B.
0

Author Comment

Glenn,   I'm posting another addition to this code.  I've got to get the dates to work in accordance to the selection.

Thanks again,

B.
0

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.