VBA for Excel 2007 tutorial-loop to sum values in cells


VBA loop: sum values in cells

In this VBA example, you will learn to use VBA loop(for loop) to sum values in Excel cells. The result of the calculation will be placed immediately below the range of values that are populated in to the cells automatically by VBA code.

To run this example VBA code, you will need a Form and a CommandButton.

VBA for Excel: vba loops to sum values in Excel cells





VBA code for this example:



Private Sub CommandButton1_Click()
    Dim s As Long
    Dim i As Integer
    s = 0
    'Populate values in Excel cells
    Range("A1") = 34
    Range("A2") = 20
    Range("A3") = 40
    Range("A4") = 50
    Range("A5") = 98
    Range("A6") = 87
    Range("A7") = 90
    Range("A8") = 78
 
    Range("A1:A8").Select 'Select the range of values to sum
    For i = 1 To Selection.Count 'Sum values inc Excel cells
        s = s + Range("A" & i)
    Next
 
    'Show result in the Excel cell below the range of values
    Range("A" & Selection.Count) = "Total: " & s
    Range("A" & Selection.Count).Font.Bold = True 'Make the font bold
 
End Sub

Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.