How to loop through a specific row of a 2-dimensional Array?-CodePudding

I have 2-dimensional array and I would like to inspect each element in a specific row with If-Then statements and assign assign values to the next row depending on the outcome of the If-Then statements? What is the correct syntax for looping through the elements of a row in a 2-d array?

CodePudding user response：

Please, try using the next Sub:

Sub changeRow(arr As Variant, iR As Long, strTxt As String)
    Dim i As Long
    For i = LBound(arr, 2) To UBound(arr, 2) '(arr, 2) to determine the number of columns
        arr(iR, i) = arr(iR, i) & strTxt
    Next i
End Sub

Of course, it can be designed to do whatever you need on the respective row. Even extending parameters to be used.

It can easily be tested in the next way:

Sub testIterate2DArrayRow()
   Dim sh As Worksheet, arr, arrR, iRow As Long, strAdd As String
   
   Set sh = ActiveSheet
   iRow = 2          'the array row to be iterated
   strAdd = " - XX"  'string to be added to each row element (instructional example)
   arr = sh.Range("A2:D6").value           'the easiest way to create a 2D array
    arrR = Application.Index(arr, iRow, 0) 'create a 1D slice of the row to be iterated/modified
                                           'if you need only iterating to extract something, you may stop here
                                           'and iterate between its elements...
    Debug.Print Join(arrR, "|")            'just to visually see the row content
   changeRow arr, iRow, strAdd             'iterate on the iRow row (and modify something)
   Debug.Print Join(Application.Index(arr, iRow, 0), "|") 'visual evidence of the modification...
End Sub

Edited:

I will let the above code for other people liking to learn the general concept.

Please, test the next code, which should process the array as (I understood) you need.

Its first lines only create the opportunity to easily check the concept. So, you should place the necessary bays on an Excel sheet, from "A1" to "J1" and run the above code. It will return the processed array starting from "L1":

Sub analizeBays()
  Dim sh As Worksheet, BayRay(), i As Long
  
  Set sh = ActiveSheet
  BayRay = sh.Range("A1:J4").value 'only to easily test the concept
  
  For i = LBound(BayRay, 2) To UBound(BayRay, 2)
        If BayRay(1, i) <= 10 Then
           BayRay(2, i) = 2035
           BayRay(3, i) = 2005
           BayRay(4, i) = 1005
        ElseIf BayRay(1, i) > 10 And BayRay(1, i) <= 12 Then
           BayRay(2, i) = 2022
           BayRay(3, i) = 1032
           BayRay(4, i) = 4344
        End If
  Next i
  'drop the processed array content starting from "L1")
  sh.Range("L1").Resize(UBound(BayRay), UBound(BayRay, 2)).value = BayRay
End Sub

CodePudding user response：

In a 2D array, it goes [row][cols], so the first array [ ] gives the rows, and the second one gives the columns. So the you'd probably want to have nested loops:

for (i = rowNumber)
    for (j = columnNumber)
         if stuff
             array[i 1][j] = stuff;
         else 
             other changes to array[i 1];
         
// increment & exit loops as needed;

Check out microsoft's docs for vb loops: https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/for-next-statement

CodePudding user response：

Loop Through a Row of a 2D Array

Option Explicit

Sub LoopThroughRow()
    
    Const RowIndex As Long = 2
    Const Criteria As Double = 3
    Const MinNum As Long = 1
    Const MaxNum As Long = 5
    
    ' Populate with random integers.
    
    Dim Data As Variant: ReDim Data(1 To 5, 1 To 5)
    
    Dim r As Long, c As Long
    
    For r = LBound(Data, 1) To UBound(Data, 1)
        For c = LBound(Data, 2) To UBound(Data, 2)
            Data(r, c) = Int((MaxNum - MinNum   1) * Rnd   MinNum)
        Next c
    Next r
    
    ' Write criteria row.
    
    For c = LBound(Data, 2) To UBound(Data, 2)
        If Data(RowIndex, c) > Criteria Then
            Data(RowIndex   1, c) = "Yes"
        Else
            Data(RowIndex   1, c) = "No"
        End If
    Next c
    
    ' Print result.

    Debug.Print "Column", "Row " & RowIndex, "Row " & RowIndex   1
    For c = LBound(Data, 2) To UBound(Data, 2)
        Debug.Print c, Data(RowIndex, c), Data(RowIndex   1, c)
    Next c

End Sub