# Random Data Generator

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.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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 Commented:
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
Commented:
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 Commented:
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
Commented:
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 Commented:
Yes!

Thanks.
0
Excel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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 Commented:
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