Incorrectly getting "Subscript out of range" Error.

[Andrew]

As you can see in the code below, I have created an array that is varying
sizes depending on the data in a worksheet called "Teams". However when I
try to assign data into that array. I get a "Subscript out of range"
"runtime error-code:9"

This happens in the first iteration when i and j are both 1. The data is
there in the tworksheet and pots is formed into (1,10) sized dimension
based on that data. Any ideas?
---
Public Sub GroupDraw()
Sheets("Teams").Activate
Static count As Integer
count = Cells(2, 6)
Static groups As Integer
groups = Cells(3, 6)
Static tpg As Integer
tpg = Cells(4, 6)
Dim pots() As Variant
ReDim pots(1 To tpg, 1 To groups) As Variant
For i = 1 To tpg
For j = 1 To groups
pots(i)(j) = Cells(i * tpg + j, 2)
Next
Next
End Sub
---

 

[Norman Jones]

Hi Andrew,

Try:

'=============>>
Public Sub GroupDraw()
Dim SH As Worksheet
Static count As Long
Static groups As Long
Static tpg As Long
Dim pots() As Variant
Dim i As Long
Dim j As Long

Set SH = ThisWorkbook.Sheets("Teams")
With SH
count = .Cells(2, 6).Value
groups = .Cells(3, 6).Value
tpg = .Cells(4, 6).Value

ReDim pots(1 To tpg, 1 To groups)
For i = 1 To tpg
For j = 1 To groups
pots(i, j) = Cells(i * tpg + j, 2).Value
Next
Next
End With
End Sub
'<<=============

 

[NickHK]

Andrew,
Do you need the variables to be static in this situation ?
And you are not using count.

Also, it is more clear which ranges you are using :
Public Sub GroupDraw()
Static count As Integer
Static groups As Integer
Static tpg As Integer
Dim pots() As Variant
Dim i As Long, j As Long

With Sheets("Teams")
count = .Cells(2, 6)
groups = .Cells(3, 6)
Debug.Print groups
tpg = .Cells(4, 6)
Debug.Print tpg

ReDim pots(1 To tpg, 1 To groups)

For i = 1 To tpg
For j = 1 To groups
pots(i, j) = .Cells(i * tpg + j, 2)
Next
Next
End With
End Sub

But the actual cause of your error is incorrect syntax for addressing the
elements of the array:
pots(i, j) = .Cells(i * tpg + j, 2)
So you are getting the correct error message....

NickHK

 

[Andrew]

Thank you,

That has solved the problem

Hi Andrew,

Try:

'=============>>
Public Sub GroupDraw()
Dim SH As Worksheet
Static count As Long
Static groups As Long
Static tpg As Long
Dim pots() As Variant
Dim i As Long
Dim j As Long

Set SH = ThisWorkbook.Sheets("Teams")
With SH
count = .Cells(2, 6).Value
groups = .Cells(3, 6).Value
tpg = .Cells(4, 6).Value

ReDim pots(1 To tpg, 1 To groups)
For i = 1 To tpg
For j = 1 To groups
pots(i, j) = Cells(i * tpg + j, 2).Value
Next
Next
End With
End Sub
'<<=============

 

[Norman Jones]

Hi Andrew,

That has solved the problem
However:

pots(i, j) = Cells(i * tpg + j, 2).Value

Should read:

pots(i, j) = .Cells(i * tpg + j, 2).Value

(I inadvertently failed to qualify the Cells range object.)

Also, note Nick's query concerning your Static variables.