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?
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.

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
KimputerCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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
Glenn RayExcel 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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.