Excel VBA For Loop with Array (5 Examples)

VBA Breakdown

• The code names the subroutine, “Array_with_Nested_ForLoop“.

Sub Array_with_Nested_ForLoop()

• We declared an integer array “MyArray” that contains six entries. The next 6 lines populate the “MyArray” array with integer values.

Dim MyArray(5) As Integer
MyArray(0) = 20
MyArray(1) = 30
MyArray(2) = 40
MyArray(3) = 50
MyArray(4) = 60
MyArray(5) = 70

• We declared a string variable “Combination_Value” and initialized it with a starting string value.

Dim Combination_Value As String
Combination_Value = "Combination Value of arrays which show = 90: "

• We will apply a For loop to go through the “MyArray” array elements from the lower bound to the upper bound. The “x” variable is used as the loop counter.

For x = LBound(MyArray) To UBound(MyArray)

• We applied a nested For loop to go through the “MyArray” array elements from the lower bound (i.e., the first element) to the upper bound (i.e., the last element). The “j” variable is used as the loop counter.

For j = LBound(MyArray) To UBound(MyArray)

• Within the inner For loop, an If statement is used to check if the sum of the current element of the “MyArray” array with the index “x” and the current element of the “MyArray” array with the index “j” is equal to 90 and if “x” is not equal to “j“. If the condition in the If statement is met, the two elements are concatenated with a “+” symbol and added to the “Combination_Value” string by using the CStr.  After the inner For loop completes, the outer For loop moves on to the next element of the “MyArray” array and restarts the j value.

If MyArray(x) + MyArray(j) = 90 And i <> j Then       
Combination_Value = Combination_Value + "," + CStr(MyArray(x)) _       
+ "+" + CStr(MyArray(j))     
End If   
Next j 
Next x

• A MsgBox statement shows the output of the final value of the “Combination_Value” string.

MsgBox (Combination_Value)

VBA Breakdown

• We named the routine “ForLoop_MultidimensionalArray()“.

Sub ForLoop_MultidimensionalArray()

• The code declares a two-dimensional array named MyArray with 4 rows and 2 columns. The Variant data type is used here to allow the array to hold values of different data types.

Dim MyArray(3, 1) As Variant

• The code then populates the array with values. The first index of the array (MyArray(x, 0)) holds the name of a participant, and the second index (MyArray(x, 1)) holds their age.

MyArray(0, 0) = "Ronin"
MyArray(0, 1) = 8
MyArray(1, 0) = "Maddison"
MyArray(1, 1) = 15
MyArray(2, 0) = "John"
MyArray(2, 1) = 13
MyArray(3, 0) = "Jill"
MyArray(3, 1) = 16

• The code declares two variables Under15 and Under20 as strings, which will later hold the names of participants under the age of 15 and 20, respectively:

Dim Under15 As String
Under15 = "Under 15 participants Name : "
Dim Under20 As String
Under20 = "Under 20 participants Name: "

• The code then uses a nested For loop to iterate through each row of the array and check if the age of the participant falls within a certain range. In this case, if the age of the participant is greater than 8 and less than 14, their name is added to the Under15.  Similarly, if their age is greater than 14 and less than 17, their name is added to the Under20 string.

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
    If MyArray(i, 1) > 8 And MyArray(i, 1) < 14 Then
      Under15 = Under15 + CStr(MyArray(i, 0)) + ",       "
    ElseIf MyArray(i, 1) > 14 And MyArray(i, 1) < 17 Then
      Under20 = Under20 + CStr(MyArray(i, 0)) + ",  "
    End If
Next i

• The code displays the names of participants under the age of 15 and 20 in separate message boxes:

MsgBox (Under15)
MsgBox (Under20)

VBA Breakdown

• The name of the subroutine is “ForEach_Loop_Array“.

Sub ForEach_Loop_Array()

• We declared a variable “MyString” as an array of 4 strings. The array is then populated with 4 names.

Dim MyString(4) As String
MyString(1) = "Robin"
MyString(2) = "Ronin"
MyString(3) = "John"
MyString(4) = "Maddison"

• We declared a variant variable “Name” to hold each element of the “MyString” array.

Dim Name As Variant

• We will apply a For Each loop to go through each element of the “MyString” array. The “Name” variable is used to hold the value of each element in turn.

For Each Name In MyString

• Within the loop, the print statement is used to output the value of the “Name” variable to the Immediate window. Now, the Next statement moves to the next element in the array until all elements have been analyzed. Therefore, when the code is performed, the For Each loop revolves around each element of the “MyString” array, assigns the value of each element to the “Name” variable, and outputs each “Name” to the Immediate window using the Debug.Print statement.

Debug.Print Name 
Next Name

VBA Breakdown

• We declared a variable “MyString” as an array of 4 strings, with indexes from 1 to 4. The array is then populated with 4 names.

Dim MyString(1 To 4) As String
MyString(1) = "Robin"
MyString(2) = "Ronin"
MyString(3) = "John"
MyString(4) = "Madison

• We declare an integer variable “j” that will be used as a loop counter in the For Next loop.
• Wwe will apply a For Next loop to go through the “MyString” array elements from index 2 to 3. The “j” variable is used as the loop counter.

  Dim j As Integer
   For j = 2 To 3

• Within the loop, the Print statement is used to output the value of the element of the “MyString” array with the index “j” to the Immediate window. Now, the Next statement moves to the next element in the loop until the specified range has been analyzed. Therefore, when the code is performed, the For Next loop goes through the elements of the “MyString” array with the indexes 2 and 3 and outputs the value of each element to the Immediate window using the Debug.Print statement.

 Debug.Print MyString(j)
   Next j

VBA Breakdown

• We declared MyArray as a variable and assign data from sheet2 into it.

MyArray = Sheet2.Cells(4, 2).CurrentRegion
reportrow = 5

• We will apply For loop to go through each row, and using the If statement, we will check the value which is greater than 20. For column operation, we will apply a For loop again to go through each column for adding the data from sheet2 into sheet3.

For Row = 2 To UBound(MyArray)
  'Using If statement
  If MyArray(Row, 1) > 20 Then
    For Column = 1 To UBound(MyArray, 2)
     Sheet3.Cells(reportrow, Column) = MyArray(Row, Column)
    Next
     reportrow = reportrow + 1
    End If
Next