SteveL said:
I need to write code that will create a spreadsheet with
20,000 cells (must be 2,000 rows, 10 columns each) and
have 2,000 of the cells filled with a number "1". The
rest of them need to have a zero "0" in the cell. The
ones have to be positioned randonly throughout the
spreadsheet.
The final results would look something like...
0 0 0 1 0 0 0 0 0 0
0 1 0 0 1 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0
0 0 1 0 0 0 0 0 0 0
etc.
Can Access do this?
--Steve
When you say "spreadsheet", do you mean an Excel spreadsheet? I didn't
feel like mucking around with automating Excel, so I created a table
(tblArray, with 10 Long Integer columns named Col0 through Col9) to hold
my array, then exported the table to Excel:
'------ start of code -----
Sub RandArray()
Dim A(1999, 9) As Integer
Dim I As Integer, J As Integer
Dim lngCount As Long
Dim lngTries As Long
Dim lngNumber As Long
Dim db As DAO.Database
Dim strSQL As String
' Generate the array.
Randomize 'Seed the random-number generator.
lngCount = 2000 'We need this many unique hits.
' Loop until we've generated "lngCount" hits.
Do While lngCount > 0
lngTries = lngTries + 1
lngNumber = Rnd() * 20000
I = lngNumber \ 10
J = lngNumber Mod 10
If A(I, J) = 0 Then
A(I, J) = 1
lngCount = lngCount - 1
End If
Loop
' Insert the array into a table.
Set db = CurrentDb
db.Execute "DELETE * FROM tblArray", dbFailOnError
For I = 0 To 1999
strSQL = vbNullString
For J = 0 To 9
strSQL = strSQL & ", " & A(I, J)
Next J
strSQL = _
"INSERT INTO tblArray Values(" & _
Mid$(strSQL, 3) & ")"
db.Execute strSQL, dbFailOnError
Next I
Set db = Nothing
DoCmd.TransferSpreadsheet _
acExport, acSpreadsheetTypeExcel9, _
"tblArray", "C:\Temp\RandArray.xls", _
False
MsgBox "Spreadsheet created."
End Sub
'------end of code -----
It has the drawback of adding the field names at the top of the
spreadsheet, but you can delete that row.
