Random Data Generator part 2

Posted on 2014-08-27
Last Modified: 2014-08-28
I have a great little random generator macro that Glenn Ray and Kimputer wrote for me.  Only problem is the date field doesn't change correctly.  When you select the Frequency, it should adjust the date to reflect the selected frequency (e.g. day, month, year, sec., etc.) against the strart date.

That's it!

Thank you in advance,

Question by:Bright01
    LVL 27

    Expert Comment

    by:Glenn Ray
    Hey...could you shorten the filename and re-attach?  EE can't handle long filenames and it's truncating the file extension.

    I'll check it out shortly.


    Author Comment


    Thank you!  

    LVL 27

    Accepted Solution

    Hey B,

    I found the error in my original code from your previous question.  The Select...Case section which determines the time increment (dblTime) was not finding "Min(s)" or "Sec(s)" and left dblTime as zero (0), so it did not show any increments.
    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("C10").Value
        intMax = Range("C9").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 "Min(s)"
                dblTime = 1 / 1440
            Case "Sec(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 updated the code to match your sheet layout (Max/Min in that order; your drawing object to run).


    Author Closing Comment

    Great!  Works like a champ.  On to the next level.  I will be using this to generate test data for sensor based readings of assets for Asset Optimization.  Thanks again....great work!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now