Subscript Out of Range error in Array...

[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

 

[Bob Phillips]

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)

 

[Jim Cone]

Kevin,
Bob found a couple of problems and so did I, plus I think a couple more.
It is 2:45 am here and I got the following code to work a few times.
No promises, but give it a try.
Right now I don't think I could give a coherent explanation of the changes...

'--------------------------------
Sub namesDigits()
Dim vArr As Variant
Dim vResult As Variant
Dim vNames As Range
Dim x As Long
Dim n As Long
Dim j As Long
Dim i As Long
Dim m As Long
Dim nRand As Long
Dim numNames As Long
Dim countNames As Long
Dim temp As String

Set vNames = Worksheets(4).Range("O3:O12").SpecialCells(xlCellTypeConstants)

numNames = vNames.Count
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
'---------------------------------------

Regards,
Jim Cone
San Francisco, CA
'*********************************

 

[Kevin Lyons]

Bob,

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

Any other ideas?

Thanks again.

Kevin

 

[Kevin Lyons]

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

 

[Jim Cone]

Kevin,

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

Regards,
Jim Cone

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 -