K
Kevin Lyons
Hello,
I am getting a Subscript Out of Range error in my array on the following
line below: vArr(i * countNames + j) = vNames(i)
I am populating a 10x10 array depending upon the number of available names
in the Range("O3:O12"). If 100 divided by the count within that range
leaves no remainder then each name should appear the same number of times
(otherwise a random number of cells are to be left blank).
I have a suspicion that the way I am populating/filling my array vNames is
causing part of the trouble: vNames = Range("O3:O" & tester + 2)
Hopefully one of you can assist.
Thanks,
Kevin
--------------------------------
Sub namesDigits()
Dim vArr, vResult, vNames As Variant
Dim x, n, j, nRand As Long
Dim numNames, countNames, tester, i, m As Integer
Dim temp As String
Worksheets(4).Activate
tester = Application.CountA(Range("O3:O12"))
vNames = Range("O3:O" & tester + 2)
numNames = tester
countNames = Int(100 / numNames)
For n = 1 To 4
Worksheets(n).Activate
ReDim vArr(1 To 100)
For i = 0 To numNames - 1
For j = 1 To countNames
vArr(i * countNames + j) = vNames(i)
Next j
Next i
For i = 100 To 2 Step -1
nRand = Int(Rnd() * 100) + 1
temp = vArr(i)
vArr(i) = vArr(nRand)
vArr(nRand) = temp
Next i
ReDim vResult(1 To 10, 1 To 10)
For i = 1 To 10
For j = 1 To 10
vResult(i, j) = vArr((i - 1) * 10 + j)
Next j
Next i
Range("C3:L12").Value = vResult
Next n
End Sub
I am getting a Subscript Out of Range error in my array on the following
line below: vArr(i * countNames + j) = vNames(i)
I am populating a 10x10 array depending upon the number of available names
in the Range("O3:O12"). If 100 divided by the count within that range
leaves no remainder then each name should appear the same number of times
(otherwise a random number of cells are to be left blank).
I have a suspicion that the way I am populating/filling my array vNames is
causing part of the trouble: vNames = Range("O3:O" & tester + 2)
Hopefully one of you can assist.
Thanks,
Kevin
--------------------------------
Sub namesDigits()
Dim vArr, vResult, vNames As Variant
Dim x, n, j, nRand As Long
Dim numNames, countNames, tester, i, m As Integer
Dim temp As String
Worksheets(4).Activate
tester = Application.CountA(Range("O3:O12"))
vNames = Range("O3:O" & tester + 2)
numNames = tester
countNames = Int(100 / numNames)
For n = 1 To 4
Worksheets(n).Activate
ReDim vArr(1 To 100)
For i = 0 To numNames - 1
For j = 1 To countNames
vArr(i * countNames + j) = vNames(i)
Next j
Next i
For i = 100 To 2 Step -1
nRand = Int(Rnd() * 100) + 1
temp = vArr(i)
vArr(i) = vArr(nRand)
vArr(nRand) = temp
Next i
ReDim vResult(1 To 10, 1 To 10)
For i = 1 To 10
For j = 1 To 10
vResult(i, j) = vArr((i - 1) * 10 + j)
Next j
Next i
Range("C3:L12").Value = vResult
Next n
End Sub