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.

Thank you in advance.

B.
D--temp-Macro-to-create-readings-v2.xls
Bright01Asked:
Who is Participating?
 
Glenn RayConnect With a Mentor 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

Open in new window


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
EE-Macro-to-create-readings-v2.xls
0
 
KimputerCommented:
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

Open in new window


It assumes cells are filled with values though, not with formulas. Please test first and see if it suits your needs?
0
 
Bright01Author 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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
KimputerConnect With a Mentor 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

Open in new window

0
 
Bright01Author 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
 
KimputerCommented:
Aha I see now, requires quite a bit of extra code. Patience please. But I'll be looking at C4 rather than C3 ?
0
 
Bright01Author Commented:
Yes!

Thanks.
0
 
Bright01Author 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
 
Bright01Author 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
All Courses

From novice to tech pro — start learning today.