Mastering VBA in Excel 365 – How to Create Macros Easily

Using For…Next Loop in Macros

By leveraging the For…Next Loop structure, you can automate the filling of cells in Excel worksheets with specific data. For…Next Loop allows you to iterate through rows or columns within a specified range, making it very effective for repeating tasks in data filling.

Example: Fill a Cell Range with Row and Column Summing Results

Here is an example of a macro that utilizes For…Next Loop to fill the range of cells A1:E10 with the value generated by the sum between the row number and the column number.

Sub Fill_Cells_With_Loop()
    Dim i As Integer, j As Integer
    For i = 1 To 10
        For j = 1 To 5
            Cells(i, j).Value = i + j
        Next j
    Next i

    With Range(“A1:E10”)
        .Font.Bold = True
        .Interior.Color = RGB(224, 255, 255)
    End With
End Sub

Code Explanation:

  • For i =  1 To 10: The first loop for iteration through rows, from 1 to 10 (rows 1 to 10).
  • For j = 1 To 5: The second loop for iterations through columns, from 1 to 5 (columns A to E).
  • Cells(i, j).Value =  i + j: Fill the cell with the sum result between the row number (i) and the column number (j).
  • With Range(“A1:E10”): Format cells A1 to E10 with bold text and light blue background.

Other Interesting Articles

Using Chr() Function to Create Characters

The Chr() function in VBA works to generate characters that correspond to ASCI codes. ASCII codes are numbers that represent characters in computer systems. For example, the 65 code indicates the letter A, while the code 66 indicates the letter B, and so on.

You can leverage the Chr() function to create macros that populate cells with characters generated from ASCII codes, either randomly or specifically.

Example: Filling a Cell Range with Random Characters Using the Chr() Function

Sub Fill_Cells_With_Random_Characters()
    Dim i As Integer, j As Integer
    Dim asciiCode As Integer

    For i = 1 To 5
        For j = 1 To 5
            asciiCode = Int(26 * Rnd) + 65
            Cells(i, j).Value = Chr(asciiCode)
        Next j
    Next i

    With Range(“A1:E5”)
        .Font.Bold = True
        .Interior.Color = RGB(255, 228, 196)
    End With
End Sub

Code Explanation:

  • Dim asciiCode As Integer: Declares a variable to store ASCII code.
  • Int(26 * Rnd) + 65: Generates a random number between 65 and 90. The 65 code is A in ASCII, and the 90 code is Z.
  • Rnd generates a random number between 0 and 1.
  • 26 * Rnd generates a number between 0 and 25 and then adds 65 to get the ASCII code between 65(A) and 90(Z).
  • Cells(i, j).Value = Chr(asciiCode): Fills a cell with characters generated by Chr() based on ASCII code.
  • With Range(“A1:E5”): Formats an A1:E5 range of cells with bold text and a peach-colored background (RGB(255, 228, 196)).

Conclusion

By utilizing Excel VBA 365, you can improve your work efficiency in Excel. Learning about macro creation and editing will provide an invaluable skill in the modern world of work, where automation is the key to success.

Latest Articles

Related Articles