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

Kevin,

This isn't a thoroughly tested answer, but a couple of observations.

The problem lies around this code

For i = 0 To numNames - 1
For j = 1 To countNames
vArr(i * countNames + j) = vNames(i)
Next j
Next i

I see 2 probloems here. Firstly you start with the counter i at 0, but the
vNames array starts at 1. Secondly, as vNames is loaded from the worksheet
range, it is created as a 2-dimensional array. This code may work better

For i = 1 To numNames - 1
For j = 1 To countNames
vArr((i-1) * countNames + j) = vNames(i, 1)
Next j
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kevin Lyons" wrote in message
...
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

Bob,

Your revision was giving me another "Subscript Out of Range" error.

Any other ideas?

Thanks again.

Kevin


Bob Phillips wrote:

Kevin,

This isn't a thoroughly tested answer, but a couple of observations.

The problem lies around this code

For i = 0 To numNames - 1
For j = 1 To countNames
vArr(i * countNames + j) = vNames(i)
Next j
Next i

I see 2 probloems here. Firstly you start with the counter i at 0, but the
vNames array starts at 1. Secondly, as vNames is loaded from the worksheet
range, it is created as a 2-dimensional array. This code may work better

For i = 1 To numNames - 1
For j = 1 To countNames
vArr((i-1) * countNames + j) = vNames(i, 1)
Next j
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kevin Lyons" wrote in message
...
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