Looping through a 3-D Array

Question

I initially asked this question How to loop through a specific row of a 2-dimensional Array? and @FaneDuru was kind enough to supply a solution but now I am hoping I can take it one step further and use a 3 dimensional array in order to obtain the item numbers needed for the second iteration I will be required to do. Initially I thought I would asssume the second iteration was the same as the first and just multiply my results by 2 but I would prefer using a 3-d Array in my solution. Here is what I got. I do not know how to display the results of the other index/iteration?

Dim SWArray() As Variant
ReDim SWArray(1 To 5, 1 To 10, 1 To 2)
SWArray(1, 1) = "Bay1"
SWArray(1, 2) = "Bay2"
SWArray(1, 3) = "Bay3"
SWArray(1, 4) = "Bay4"
SWArray(1, 5) = "Bay5"
SWArray(1, 6) = "Bay6"
SWArray(1, 7) = "Bay7"
SWArray(1, 8) = "Bay8"
SWArray(1, 9) = "Bay9"
SWArray(1, 10) = "Bay10"

SWArray(2, 1) = Bay1
SWArray(2, 2) = Bay2
SWArray(2, 3) = Bay3
SWArray(2, 4) = Bay4
SWArray(2, 5) = Bay5
SWArray(2, 6) = Bay6
SWArray(2, 7) = Bay7
SWArray(2, 8) = Bay8
SWArray(2, 9) = Bay9
SWArray(2, 10) = Bay10

'Loop through bays to assign purlin, girt and 
'formboard item numbers per the dimension

For k = LBound(SWArray, 3) To UBound(SWArray, 3)

For i = LBound(SWArray, 2) To UBound(SWArray, 2)
    If SWArray(2, i) = 0 Then
    SWArray(2, i) = 0
    SWArray(3, i) = 0
    SWArray(4, i) = 0
    ElseIf SWArray(2, i) > 6 And SWArray(2, i) <= 10 Then
    SWArray(2, i) = 2035
    SWArray(3, i) = 2754
    SWArray(4, i) = 2004
    ElseIf SWArray(2, i) > 10 And SWArray(2, i) <= 12 Then
    SWArray(2, i) = 2036
    SWArray(3, i) = 2755
    SWArray(4, i) = 2005
    ElseIf SWArray(2, i) > 12 And SWArray(2, i) <= 14 Then
    SWArray(2, i) = 2037
    SWArray(3, i) = 2756
    SWArray(4, i) = 2006
    ElseIf SWArray(2, i) > 14 And SWArray(2, i) <= 16 Then
    SWArray(2, i) = 2038
    SWArray(3, i) = 2757
    SWArray(4, i) = 2007

    End If

    Next i

    Next k

    Worksheets("Data").Range("A55").Resize(UBound(SWArray), 
    UBound(SWArray, 2)).Value = SWArray

Answer 1

The next piece of code will show how a 3D array is loaded and how its elements will be extracted by iteration. In order to make the example eloquent, please prepare two Excel sheets, in the workbook keeping the next code (ThisWorkbook), named Test_1 and Test_2. Please, place 10 (different) headers on their first row and fill 5 rows of each with different values. Then, copy the next code in a standard module and run it:

Sub testIterate3DArrayExcelExample()
 Dim SWArray(1 To 5, 1 To 10, 1 To 2)
 Dim wb As Workbook, iRow As Long, iCol As Long, iSht As Long
 
 Set wb = ThisWorkbook
 For iRow = 1 To UBound(SWArray, 1)
    For iCol = 1 To UBound(SWArray, 2)
        For iSht = 1 To UBound(SWArray, 3)
            SWArray(iRow, iCol, iSht) = wb.Worksheets("Test_" & iSht).cells(iRow, iCol)
        Next iSht
    Next iCol
 Next iRow
 
 Dim i As Long, j As Long, k As Long
 For i = 1 To UBound(SWArray, 1)
    For j = 1 To UBound(SWArray, 2)
        For k = 1 To UBound(SWArray, 3)
            Debug.Print "Sheet Test_" & k & ", Column " & j & ", Row " & i & ": " & SWArray(i, j, k)
        Next k
    Next j
 Next i
End Sub

You can see that for the last dimension all the previous two dimension elements must exist.

So SWArray(1, 1) = "Bay1" does not make any sense..

I am waiting for your clarification regarding what you want accomplishing and I will try helping with a different solution.

If something not clear enough in the above code/sheets preparations, do not hesitate to ask for clarifications.

Edited:

Looking to the previous question and your comments, I tried deducing what you really want accomplishing and I would like to propose the next solution. It involves extending the second array dimension (columns) with an element (which can be 1 or 2) (I mean 11 columns instead of 10 and the last one to be the ID for selecting between the two situations), iterate by columns excepting the last one, and fill two separate arrays according to this last element value. The processed result for each array will be returned starting from "M1") (first processed array) and starting from "X1" the second one:

Sub analizeBaysTwoOptions()
  Dim sh As Worksheet, SWArray(), SWArray1(), SWArray2(), i As Long
  Dim k1 As Long, k2 As Long
  
  Set sh = ActiveSheet: k1 = 1: k2 = 1
  'last column element (in K:K) column, should be the idendifier for the two situations:
  SWArray = sh.Range("A1:K4").value 'only to easily test the concept
  ReDim SWArray1(1 To UBound(SWArray), 1 To UBound(SWArray, 2) - 1) '- 1 to except the last element
  ReDim SWArray2(1 To UBound(SWArray), 1 To UBound(SWArray, 2) - 1) '- 1 to except the last element
  
  For i = LBound(SWArray, 2) To UBound(SWArray, 2) - 1 '- 1 to exclude last column from iteration
        If SWArray(1, UBound(SWArray, 2)) = 1 Then
            If SWArray(1, i) <= 10 Then
               SWArray1(1, k1) = SWArray(1, i)
               SWArray1(2, k1) = 2035
               SWArray1(3, k1) = 2005
               SWArray1(4, k1) = 1005: k1 = k1 + 1
            ElseIf SWArray(1, i) > 10 And SWArray(1, i) <= 12 Then
               SWArray1(1, k1) = SWArray(1, i)
               SWArray1(2, k1) = 2022
               SWArray1(3, k1) = 1032
               SWArray1(4, k1) = 4344: k1 = k1 + 1
            End If
        Else
            'Stop
            'use a different lagorithm (or not) and load SWArray2()
            If SWArray(1, i) <= 10 Then
               SWArray2(1, k2) = SWArray(1, i)
               SWArray2(2, k2) = 2035
               SWArray2(3, k2) = 2005
               SWArray2(4, k2) = 1005: k1 = k1 + 1
            ElseIf SWArray(1, i) > 10 And SWArray(1, i) <= 12 Then
               SWArray2(1, k2) = SWArray(1, i)
               SWArray2(2, k2) = 2022
               SWArray2(3, k2) = 1032
               SWArray2(4, k2) = 4344: k2 = k2 + 1
            End If
        End If
  Next i
  'drop the processed arrays content:
  sh.Range("M1").Resize(k1 - 1, UBound(SWArray1, 2)).value = SWArray1
  sh.Range("X1").Resize(k2 - 1, UBound(SWArray1, 2)).value = SWArray2
End Sub

The code can easily be adapted to return in different sheets.

It is not tested (no time to build the sheet environment...), but this should be the concept. If something goes wrong, please specify what error on what code line.

Now I need to go out. Please, examine the about supposed solution and send some feedback. If my assumption is not a correct one, please better define your need and I will try helping when I will be back.