WorksheetFunction.Sum with For loop-CodePudding

Screenshot

Hi! I'm posting this question in the hope it hasn't already been answered!

As you can see in the image, I would like to get the area under the curve using VBA. The results of the trapezoid method using the simple formula (B4 - B3) * (C4 C3) / 2 are given in the column E (light yellow shade) and the sum is given in the cell F4, which is the area I want.

To test wether my VBA code works, I limited it to calculate the sum of the first two trapezoids, but it gives me the surface of the second trapezoid instead:

Sub Integral()

Dim i As Integer
Dim Integral As Double

For i = 1 To 2

    Integral = Application.WorksheetFunction.Sum((Cells(3   i, 2) - Cells(2   i, 2)) * (Cells(3   i, 3)   Cells(2   i, 3)) / 2)

Next i

Sheet1.Cells(4, 7) = Integral

End Sub

I believe the solution is simple, but at the moment, nothing comes to my mind! I would appreciate it if someone showed me how to solve this! Thank you in advance!

CodePudding user response：

You dont have an accululative sum after each iteration of the loop

Sub Integral()

Dim i As Integer
Dim Integral As Double
Dim Sum_Area as Double 

For i = 1 To 2

    Integral = (Cells(3   i, 2) - Cells(2   i, 2)) * (Cells(3   i, 3)   Cells(2   i, 3)) / 2
    Sum_Area = Sum_Area   Integral
Next i

Sheet1.Cells(4, 7) = Sum_Area

End Sub