Printable View
hi there,
I've been having a little trouble creating code for this problem
(as a novice user to vba excel).
What I'm trying to achieve:
The user enters a number, which goes into cell a1 (which is no problem). I would then, based on the "a1" cell value create a n by n random number array & then output it in any cell range.
ie, if a = 3, then there would be an array with random numbers in all 9 cells.
Greatly appreciate any help, thanks !!
If I could ask about a few details:
I'm guessing that the array is always square, with each dimension equal to the users entry.
What are the elements of the random array? Integers between 1 and A1^2?
Are duplicates allowed?
yep, duplicates are allowed
also the element you've posted are fine.
i was thinking about maybe putting in the cells, the function "=rand()"
I had come up with this, but it doesn't work :( ...
Private Sub but1_Click()
Dim i As Integer, j As Integer
Dim n As Integer, m As Integer
Dim Arr(1 To n, 1 To n)
n = Worksheets("sheet1").Range("a1").Value
For i = 1 To n
Cells(i, 1) = i
Next i
For j = 1 To n
Cells(1, j) = j
Next j
End Sub
I think this may work but you gotta change the upper/lower bound of the two nested For..Next loops if you want the output range to be somewhere else.
[vba] Public Sub RandMeUp()
Dim UserInput As Long, RowCounter As Long, _
ColCounter As Long
Dim TargetSheet As Worksheet
Set TargetSheet = Worksheets("Sheet1")
'Change the formula to suit needs
Const FormulaString As String = "=RAND()"
With TargetSheet
UserInput = .Cells(1, 1).Value
'Populates from range B1 onwards
For RowCounter = 2 To UserInput + 1
For ColCounter = 1 To UserInput
.Cells(RowCounter, ColCounter).FormulaR1C1 = _
FormulaString
Next ColCounter
Next RowCounter
End With
Set TargetSheet = Nothing
End Sub[/vba]
I think this might do what you want. It allows duplicates. The source cell (A1 in your example) and the range where you want the results need to be adjusted to meet your situation.
[VBA]Sub trial()
Dim sourceRange As Range
Dim putRange As Range
Dim Size As Long
Dim addressStr As String
Set sourceRange = ThisWorkbook.Sheets("sheet1").Range("a1"): Rem adjust the source
Set putRange = ThisWorkbook.Sheets("sheet1").Range("c3"): Rem adjust the destination
Size = CLng(Val(sourceRange.Range("a1").Value))
addressStr = sourceRange.Parent.Name & "!" & sourceRange.Address(True, True, xlR1C1)
If 0 < Size Then
With putRange.Range("a1").Resize(Size, Size)
.FormulaR1C1 = "=INT(RAND()*" & addressStr & "^2)+1"
Calculate
.Value = .Value
End With
End If
End Sub[/VBA]
Later: this version is easier to adapt. Passing arguments to this routine could be the control mechanism.