PDA

View Full Version : "Subscript out of range" ... Issue



mcarthurgdm
03-31-2015, 10:43 AM
So, below I have a Variant array. This is supposed to be a 126x12 array. I want to take the elements of this array and then to place them into a single column.

Where the bold faced text is, I get an "Subscript out of range" error. I have no idea why. I get to the first element of the loop and it kicks out. Anyone have any idea why I would get such an error? The way I defined my matrices and variables seems correct and I know that the numbers match up correctly as well. .

The issue lies within: BSAAMLValue(s, ZZ)

Any help is appreciated.

I placed comments in the code below to help illustrate where the problem is.




Dim BSAAMLValue(126, 12) As Variant
Dim main_count As Integer

'This code takes elements from a worksheet and transfers them into an 126x12 array.
For Z = 1 To 12

For N = 1 To 126
BSAAMLValue(N, Z) = wb.Worksheets("BSA|AML").Cells(14 + (N - 1), Z + 6).Value
Next N

Next Z

'This code takes those array elements and transfers them into a single column matrix. This is done using the main_count variable.
main_count = 2
For ZZ = 1 To 12

For s = 1 To 126
mainwb.Worksheets("Import_Template").Cells(main_count, 8).Value = BSAAMLValue(s, ZZ)
main_count = main_count + 1
Next s


Next ZZ

mancubus
03-31-2015, 12:37 PM
welcome to the forum.

put Option Base 1 at top of the module.

or declare the variable like Dim BSAAMLValue(1 To 126, 1 To 12) As Variant

mancubus
03-31-2015, 12:39 PM
Option Base 1


Sub test()

Dim BSAAMLValue(126, 12) As Variant
Dim i As Long, j As Long, main_count As Long

'This code takes elements from a worksheet and transfers them into an 126x12 array.
For j = 1 To 12
For i = 1 To 126
BSAAMLValue(i, j) = wb.Worksheets("BSA|AML").Cells(14 + (i - 1), j + 6).Value
Next i
Next j

'This code takes those array elements and transfers them into a single column matrix. This is done using the main_count variable.
main_count = 2
For j = 1 To 12
For i = 1 To 126
mainwb.Worksheets("Import_Template").Cells(main_count, 8).Value = BSAAMLValue(i, j)
main_count = main_count + 1
Next i
Next j

End Sub

snb
04-01-2015, 12:44 AM
The first 2 loops are redundant:
Reduce the reading/writing operations as much as possible.


sub M_snb()
sn =sheets("BSA|AML").Cells(14 ,6).resize(126,12)
redim sp(1 to ubound(sn)*ubound(sn,2),0)

for j=1 to ubound(sp)
sp(j,0)=sn( (j -1) \ ubound(sn,2) +1,(j-1) mod ubound(sn,2)+1)
next

sheets("Import_Template").Cells(2, 8).resize(ubound(sp))=sp
End sub