Subscript out of range - using an Array and For

[JPLouw]

Hi,

I am using Excel 2010. I need to do data manipulation for a quantitative model, I and have encountered trouble with reading and manipulating data from Excel using an array. I must use an Array as I am testing a process which will later be translated for the database using Java (data manipulation must use an indexing approach). I have provided a very simple element of the overall code, which highlights the problem. I keep getting an Error Code 9 - Subscript out of Range. Can you please help me with this problem.

Sub Array2()
'
' Array2 Macro
'
Dim MyComparison() As Variant
Dim n As Long

MyComparison() = ThisWorkbook.Worksheets("Sheet1").Range("A2:A6").Value

Range("C2").Activate

For n = LBound(MyComparison) To UBound(MyComparison)
ActiveCell.Value = MyComparison
ActiveCell.Offset(1, 0).Activate
Next n

End Sub

 

[VoG]

Assigning a range to an array produces a 2 dimensional array. Try

Sub Array2()
'
' Array2 Macro
'
Dim MyComparison() As Variant
Dim n As Long

MyComparison = ThisWorkbook.Worksheets("Sheet1").Range("A2:A6").Value

Range("C2").Activate

For n = LBound(MyComparison, 1) To UBound(MyComparison, 1)
ActiveCell.Value = MyComparison(n, 1)
ActiveCell.Offset(1, 0).Activate
Next n

End Sub

 

[JPLouw]

Hi VoG,

It works perfectly, thank you very much for your help.

Kind regards