Using arrays in a loop-CodePudding

Each page (01,02...11,12) represents a month of the year. Each page has different staff and salaries. I want to see the workers data on these pages in one page. While doing this, I need to do some operations. Since it would be slow in terms of speed, I wanted to do the operations inside the arrays instead of copying this data to a page and performing the operations there. What I actually want to do and learn. We can navigate pages one by one in a loop. I want to do the same for arrays (1st array, 2nd array, ... 11 array, 12 array).

Option Explicit
Option Base 1

Dim d1() As Variant, d2() As Variant, d3() As Variant, d4() As Variant, d5() As Variant, d6() As Variant
Dim d7() As Variant, d8() As Variant, d9() As Variant, d10() As Variant, d11() As Variant, d12() As Variant
Dim personel As Collection, sonuc() As Variant, d() As Variant

Sub Example()
    Dim i As Integer
    For i = 1 To Worksheets.Count
        Debug.Print Worksheets(i).Name
    Next i
End Sub
 
Sub Example2()
    Dim i As Integer
    For i = 1 To 12
        ' ???? d & i = Worksheets(i).Range("A1:AA34")
    Next i
End Sub

CodePudding user response：

I recommend creating a 2 dimensional array of data where each elemnt holds the information for a single worksheet. Creating an Employee class would make the life easier in the long run. WatchClass Modules Explained in Simple English (with examples)

Sub Main()
    Dim Data As Variant
    Data = AllWorkSheetData
    Stop
End Sub

Function AllWorkSheetData() As Variant()
    Dim Result As Variant
    ReDim Result(1 To Worksheets.Count)
    
    Dim i As Long
    For i = 1 To Worksheets.Count
        Result(i) = Worksheets(i).Range("A1:AA34")
    Next i
    
    AllWorkSheetData = Result
End Function

Result Image

CodePudding user response：

Do you need 12 different arrays for your operation? If you want to paste the data one after the other on a single sheet, you can use a single array and reassign values in each iteration.

Dim d() As Variant, i As Integer
For i = 1 to Worksheets.Count
    d = Worksheets(i).Range("A1:AA34").Value2
    'Do your calculations with d array.
    '
    '
    '
    'Paste d array in aggregate sheet.
Next i

Also, use Excel object datatypes along with For Each loop instead of integers. It is much better to understand.

Dim ws As Worksheet
Dim d() As Variant
For Each ws In Worksheets
    d = ws.Range("A1:AA34").Value2
    'Do your calculations with d array.
    '
    '
    '
    'Paste d array in aggregate sheet.
Next ws

CodePudding user response：

You need to create an array to hold variants that you are connecting on to the data from your sheet:

Dim d(1 To 12) As Variant

Dim i As Integer
For i = 1 To 12
          d(i) = Worksheets(i).Range("A1:AA34")
          Debug.Print d(i)(1, 1) ' access one the value from one cell of the sheet that has been read into the array.
Next i
End Sub