0

I got a problem from my work.

I have to read data from a sheet and save it to lists. I thought about a loop, because it have to be dynamic lists, because I don't want to lose the data in the next loop.

Firstly, I count the number of Sheet's column, and secondly, the rows of the indexer column. For example: for i=1 to maxHorizont 'horizontal for j =1 to maxVertic 'vertical

readColumn would be the fix part of the name and I want to add the 'i' (indexer) to it.

The result would be this: DIM readColumn1 as Object, DIM readColumn2 as Object, DIM readColumn3 as Object, . . .

Could you have an ide to fix my problem?

braX
  • 11,506
  • 5
  • 20
  • 33
Bence
  • 1
  • 2
  • 1
    You can't define variable names on the fly like that (just not something vba supports). – Spinner Nov 20 '21 at 21:23
  • 2
    Use an array instead. – braX Nov 20 '21 at 21:43
  • At any point of time you think that you have to declare multiple variables that serve the similar purpose and need to refer them by index - Think array (or collection). – Raymond Wu Nov 21 '21 at 01:50
  • Some helpful links: [Create an array from range](https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba), [2-dim array from range](https://stackoverflow.com/questions/18481330/2-dimensional-array-from-range/18481730#18481730), [Slice an array](https://stackoverflow.com/questions/175170/how-do-i-slice-an-array-in-excel-vba) – T.M. Nov 21 '21 at 09:36
  • Use either [Collection](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/collection-object) or Array – eren Nov 24 '21 at 11:32

1 Answers1

0

The best way would be using arrays.

Supossing you want to get Double values, your array should be declared like that:

ReDim readColumn1(0 To maxVertic) As Double

them you can pass it through a loop:

For i = 0 To maxVertic
     readColumn1(i) = worksheets("your_sheet").cells(i, your_column)
Next i

Using that logic, create one array for each column.

Felipe Mezzarana
  • 87
  • 7