help w/ vba code

Posted by harry on December 13, 2001 12:21 AM

i need to make a macro that takes two columns of values (x,y) and calculates the integral by using the formula SUM[(x2-x1)*(y2-y1) + (x3-x2)*(y3-y2) + ...] and loops for any length list of numbers ie and outputs the summed value in a single cell.

Posted by Dan on December 13, 2001 9:16 AM

Assuming x and y values are in A and B columns, respectively, and your data starts in A1, B1. Try this macro that puts the answer in C1:

Sub Integral()
Dim Subtotal, x1, x2, y1, y2 As Long
Subtotal = 0
Start = 1
'Starting row of data, change as necessary

Rw = Start
Do Until IsEmpty(Cells(Rw, 1))
Rw = Rw + 1
Loop
'Counts number of rows in column A

For i = Start + 1 To Rw - 1
x1 = Cells(i - 1, 1)
x2 = Cells(i, 1)
y1 = Cells(i - 1, 2)
y2 = Cells(i, 2)
Subtotal = Subtotal + ((x2 - x1) * (y2 - y1))
Next i
'Performs the math

Range("C1") = Subtotal

End Sub