Subscript Out of Range error in Array...
 

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

Subscript Out of Range error in Array...
 

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

Subscript Out of Range error in Array...
 

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

Subscript Out of Range error in Array...
 

Kevin,

I'm sorry, I just don't have time to look at it now.

Regards,
Jim Cone

"Kevin Lyons" wrote in message
...
Jim,
It appears to be working okay, with the exception of my test scenario of 8
names.
Only the top seven names in the range array get populated into the grid.
Any ideas as to what is happening?
I am going to try to debug it as well.
Thanks again.
Kevin

- snip -