Simple Array in Excel 1

[tbg130]

Hi All,

I'm having a bit of trouble populating an array in Excel. Here is the code I have tried and I get a 'subscript out of range error. Any thoughts?

For k = 1 To topValues
Top5Countries(k) = Worksheets(worksheetName).Cells(4 + k, countryColumn)
Next k

For l = 1 To topValues
Top5Values(l) = objbook.Worksheets(worksheetName).Cells(4 + l, dataColumn)
Next l

Thanks alot!

 

[tbg130]

Should have shown code including variable/array declarations... are they correct?


Dim k As Integer, l As Integer
Dim Top5Countries(), Top5Values()

For k = 1 To topValues
Top5Countries(k) = Worksheets(worksheetName).Cells(4 + k, countryColumn)
Next k

For l = 1 To topValues
Top5Values(l) = objbook.Worksheets(worksheetName).Cells(4 + l, dataColumn)
Next l

 

[Zathras]

The compiler is telling you that the first value of k is "out of range" since the first value of k is 1, that means that you haven't allocated memory for even one element in your array(s).

If you know in advance the maximum value for "topValues" then you can use this (e.g. if topValues = 5) to reserve memory for 5 elements of each array:

Dim Top5Countries(5), Top5Values(5)

Alternatively, you can leave the arrays "dynamic" and insert these lines in front of the first for statement to allocate the memory at that point:

    ReDim Top5Countries(topValues)
    ReDim Top5Values(topValues)

The choice is yours.