Seek UBound and Loop Code Explanation

[myespooir]

Hi Forum,

I don't understand the code below, wondering if you could help explain it to me?

1) What is the code looping?
2) What is the value of UBound(brr, 2) and UBound(arr)?
3) in Cells(s, 3).Resize(x, 1) = arr(i, 1), what does the = arr(i, 1) do?
4) in Cells(s, 8).Resize(x, 1) = Application.Transpose(Application.Index(brr, 1, 0)), what does = Application.Transpose(Application.Index(brr, 1, 0)) do?

Thanks a lot,
Christina

(Just a note)
'Row=17; Col=BC

With Sheets("Assist")
    arr = .Range("b5:b" & Row)
    brr = .Range("d2:" & Col & Row)
    crr = .Range("c5:c" & Row)
    End With

If Row > 5 Then
s = 28: x = UBound(brr, 2)
For i = 1 To UBound(arr)
    Cells(s, 3).Resize(x, 1) = arr(i, 1)
    Cells(s, 4).Resize(x, 1) = crr(i, 1)
    Cells(s, 8).Resize(x, 1) = Application.Transpose(Application.Index(brr, 1, 0))
    Cells(s, 9).Resize(x, 1) = Application.Transpose(Application.Index(brr, 2, 0))
    Cells(s, 10).Resize(x, 1) = Application.Transpose(Application.Index(brr, 3, 0))
    Cells(s, 17).Resize(x, 1) = Application.Transpose(Application.Index(brr, i + 3, 0))
    s = s + x
Next i
End If

 

[Jerry Sullivan]

Hi Christina, The purpose of the code appears to be take values from 3 ranges of Sheet Assist and then following a pattern paste all the combinations of those values into separate rows beginning at Row 28 on the ActiveSheet.

Here's a screen shot to help visualize the pattern. This is similar to the starting source data and it uses cell addresses as the values of the cells.
This example stops at Column G, whereas the code in the OP goes to Column BC.

Excel 2013
	B	C	D	E	F	G
1			brr->
2			$D$2	$E$2	$F$2	$G$2
3			$D$3	$E$3	$F$3	$G$3
4	arr	crr	$D$4	$E$4	$F$4	$G$4
5	$B$5	$C$5	$D$5	$E$5	$F$5	$G$5
6	$B$6	$C$6	$D$6	$E$6	$F$6	$G$6
7	$B$7	$C$7	$D$7	$E$7	$F$7	$G$7
8	$B$8	$C$8	$D$8	$E$8	$F$8	$G$8
9	$B$9	$C$9	$D$9	$E$9	$F$9	$G$9
10	$B$10	$C$10	$D$10	$E$10	$F$10	$G$10
11	$B$11	$C$11	$D$11	$E$11	$F$11	$G$11
12	$B$12	$C$12	$D$12	$E$12	$F$12	$G$12
13	$B$13	$C$13	$D$13	$E$13	$F$13	$G$13
14	$B$14	$C$14	$D$14	$E$14	$F$14	$G$14
15	$B$15	$C$15	$D$15	$E$15	$F$15	$G$15
16	$B$16	$C$16	$D$16	$E$16	$F$16	$G$16
17	$B$17	$C$17	$D$17	$E$17	$F$17	$G$17
Assist

Here is the result of running the macro and stopping it after just two rows of source data have been processed.
Hopefully the colors help illustrate the pattern.

Excel 2013
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
26
27	arr	crr				brr-row1	brr-row2	brr-row3							brr-rows 4-16
28	$B$5	$C$5				$D$2	$D$3	$D$4							$D$5
29	$B$5	$C$5				$E$2	$E$3	$E$4							$E$5
30	$B$5	$C$5				$F$2	$F$3	$F$4							$F$5
31	$B$5	$C$5				$G$2	$G$3	$G$4							$G$5
32	$B$6	$C$6				$D$2	$D$3	$D$4							$D$6
33	$B$6	$C$6				$E$2	$E$3	$E$4							$E$6
34	$B$6	$C$6				$F$2	$F$3	$F$4							$F$6
35	$B$6	$C$6				$G$2	$G$3	$G$4							$G$6
Assist

I've annotated the code including comments that respond to your questions.

Sub LoopingThruArrays()
 Dim Row As Long
 Dim i As Long, s As Long, x As Long
 Dim Col As String
 
 Dim arr As Variant, brr As Variant, crr As Variant
 
 Row = 17
 Col = "BC"

 With Sheets("Assist")
  '--these statements write the values in each specified range
  '     into 2D Variant Arrays. The elements of each array can be
  '     referenced by row and column indicies within the 2D matrix.
  '     for example: arr(RowIndex,ColumnIndex)
   arr = .Range("b5:b" & Row)
   brr = .Range("d2:" & Col & Row)
   crr = .Range("c5:c" & Row)
 End With

 '--test that the last row isn't less than first row
 If Row > 5 Then
   '--s will be the top row of the range where values will be written.
   s = 28
   
   '--UBound gets the upperbound or maximum index number for a dimension
   '    UBound(brr,1) is the max Row Index which is the number of Rows of elements (16)
   '    UBound(brr,2) is the max Col Index which is the number of Columns of elements (54)
   x = UBound(brr, 2)
   
   '--Ubound(arr) can be used instead of the equivalent Ubound(arr,1)
   '    to get the number or rows of elements (13)
   
   '  This will loop through i= 1 to 13 (for each row of the array)
   For i = 1 To UBound(arr)
   '--write values stored in arrays
      
      '--write each value of arr (that came from b5:b17) 55 times (once for each col of brr)
      '    beginning at c28 and going down col c in blocks of 55.
      ' --as the code loops arr(i, 1) is the value stored in row index i, column 1 of the array arr
      Cells(s, 3).Resize(x, 1) = arr(i, 1)
      
      '--write each value of crr 55 times beginning at d28
      '    and going down col d in blocks of 55.
      Cells(s, 4).Resize(x, 1) = crr(i, 1)
      
      '--write the values from the first row of brr 55 times beginning at h28
      '--Application.Index(brr, 1, 0)) uses the worksheet function Index to reference an entire
      '      row of elements in the 2D array
      '-- Application.Transpose transforms the row of values into a column of values
      Cells(s, 8).Resize(x, 1) = Application.Transpose(Application.Index(brr, 1, 0))
      
      '--write the values from the second row of brr 55 times beginning at i28
      Cells(s, 9).Resize(x, 1) = Application.Transpose(Application.Index(brr, 2, 0))
      
      '--write the values from the third row of brr 55 times beginning at j28
      Cells(s, 10).Resize(x, 1) = Application.Transpose(Application.Index(brr, 3, 0))
            
      '--write the values from the 4th-16th row of brr 55 times beginning at q28
      Cells(s, 17).Resize(x, 1) = Application.Transpose(Application.Index(brr, i + 3, 0))
      s = s + x
   Next i
 End If
End Sub

I should note that 2D arrays don't actually have Rows and Columns. That terminology is just used to make it easier to visualize the matrix.