Randomly populate Range selection with 14 values 1

[MSBrady]

Oy!

Excel 2000
2 Worksheets: "Sample Report" & "Contracts"
Creating test data

"Sample Reports.xls" contains approx. 40,000 rows. I want to populate the entire 'Sample Report'!H:H column with randomly generated values based on the data in Contracts!A2:A14.
I have used

Sub RandDates()
    Dim MyRange As Range
    Dim dtMin As Date, dtMax As Date
    Dim dtRand As Date

    ' If selection is not Excel Range
    If TypeName(Selection) <> "Range" Then Exit Sub

    Set MyRange = Selection

    ' Get Min and Max value
    ' From: 1/1/1990 (put your start Date)
    dtMin = #1/1/1990#
    ' To: Today
    dtMax = Date

    Randomize
    Application.ScreenUpdating = False

    For Each C In MyRange.Cells
        ' Calculate random value, where
        ' Value >= Min And Value <= Max
        dtRand = Rnd * (dtMax - dtMin) + dtMin
        dtRand = Int(dtRand)

        C.Value = dtRand
        ' Change format for cell, below, as desired
        C.NumberFormat = "m/d/yyyy"
    Next C

    Application.ScreenUpdating = True
End Sub

to successfully generate random dates for 'Sample Report'!A:A column.
I have tried

Sub RandData()
    Dim MyRange As Range
    Dim MyArray() As Variant
              
    ' If selection is not Excel Range
    If TypeName(Selection) <> "Range" Then Exit Sub

    Set MyRange = Selection
    MyArray = Range("Contract!$A$2:$A$14").Value
    
    For C = 1 To MyRange.Count
        MyRange.Value = MyArray
    Next C

End Sub

to accomplish my purposes, however it only populates 13 cells regardless of the number of cells selected. I would rather not repeat Cut and Paste 3077 times.

any ideas?

TIA

 

[PHV]

Perhaps something like this ?
Set MyRange = Selection
For Each C In MyRange.Cells
C.Value = Range("Contract!$A$" & Int(2 + 12 * Rnd)).Value
Next C



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MSBrady]

PHV, You rock my face off!
thanks

 

[MSBrady]

seems like an inordinately long post for such a simple solution. :0) but you guys are pros right?
BTW is 40,000 rows too large for Excel? I have imported all this into Access and am running swimmingly, but just for my own knowledge.
thanks

 

[Loomah]

Just for your "own knowledge":
40,000 isn't too large for excel, now! Since version 8 (xl97) there have been 65,536 rows in a sheet. Prior to that I can't remember the exact figure (it probably was half what it is now) but it was certainly in the region of 32k

There are also 256 columns givin something like 16.77 million cells.

This can't be increased. No way, No how. Ever!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

....or wait for Office 12 and get lots more rows......

40,000 rows isn't technically too large for Excel as Loomah has stated. Practically, however, 20k - 30k is about all excel can handle if you are going to be manipulating the data in any way. If it is just going to be stored as a list then you will be fine. If you need to move it / change it / aggregate it / summarise it etc etc, you may find that performance is unacceptably slow with that amount of data.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question