Excel VBA - Array and For Loop problem

  • Thread starter Thread starter ajlove20
  • Start date Start date
A

ajlove20

Hi,

I have been given an assignment that has 3 columns. The colums appea
as follows:

Point No. X Value Y Value
1 0 0
2 0.2 0.45
3 0.4 0.90
...
100 19.8 877.52
101 20 927.89


I am supposed to read the values and store them in arrays and use Fo
loops to determine the sum of the even numbered points and the sum o
the odd numbered points. In the long run, I am supposed to us
Simpson's Rule to calculate the area under the curve of y vs x. I wa
wondering how I would solve this using vba code.

Thank you in advance for your help.

a
 
One way:

Dim vArr As Variant
Dim i As Long
Dim dblSum As Double

vArr = Range("B1:C101").Value
For i = LBound(vArr, 1) To UBound(vArr, 1) Step 2
dblSum = dblSum + vArr(i, 2)
Next i

etc.
 
you can try this...

Sub test()
Dim v As Variant
Dim i As Integer
Dim oddtotal As Double
Dim eventotal As Double

v = Range("A1:C101").Value

For i = LBound(v, 1) To UBound(v, 1) Step 2
oddtotal = oddtotal + v(i, 2) + v(i, 3)
v(i, 1) = ""
Next i

For i = LBound(v, 1) To UBound(v, 1) Step 1
If v(i, 1) <> "" Then
eventotal = eventotal + v(i, 2) + v(i, 3)
v(i, 1) = ""
End If
Next i

MsgBox oddtotal
MsgBox eventotal
End Sub

JSP
 
you can try this... ...
For i = LBound(v, 1) To UBound(v, 1) Step 2
oddtotal = oddtotal + v(i, 2) + v(i, 3)
v(i, 1) = ""
Next i

For i = LBound(v, 1) To UBound(v, 1) Step 1
If v(i, 1) <> "" Then
eventotal = eventotal + v(i, 2) + v(i, 3)
v(i, 1) = ""
End If
Next i
Click to expand...

I'll bite: why not one loop?


For i = LBound(v, 1) To UBound(v, 1)

If i Mod 2 = 1 Then
oddtotal = oddtotal + v(i, 2) + v(i, 3)

Else
eventotal = eventotal + v(i, 2) + v(i, 3)

End If

Next i
 
Just another general plan of attack...

v = Range("B1:C101").Value
On Error Resume Next
For p = LBound(v, 1) To UBound(v, 1) Step 2
OddTotal = OddTotal + v(p, 2)
EvenTotal = EvenTotal + v(p + 1, 2)
Next p
 
Back
Top