Subscript Out of Range error in Array...

  • Thread starter Thread starter Kevin Lyons
  • Start date Start date
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
 
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,
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
'*********************************
 
Bob,

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

Any other ideas?

Thanks again.

Kevin
 
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
 
Kevin,

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

Regards,
Jim Cone

Kevin Lyons said:
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
Click to expand...

- snip -
 
Back
Top