Using arrays in a loop

Question

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

TinMan · Accepted Answer · 2022-03-20 18:00:30Z

2

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

answered Mar 20, 2022 at 18:00

TinMan

7,8142 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hrishikesh Nadkarni · Accepted Answer · 2022-03-20 18:09:24Z

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

Gove · Accepted Answer · 2022-03-20 20:19:07Z

0

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

edited Mar 20, 2022 at 20:19

answered Mar 20, 2022 at 17:51

Gove

1,82411 silver badges12 bronze badges

Collectives™ on Stack Overflow

Using arrays in a loop

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related