PDA

View Full Version : Solved: Help with automatically fill in cells by VBA loops



qpham26
02-23-2013, 08:11 AM
Hi, I am trying to use do while loop, to calculate the x and y position of a cannonball is fired from a cannon. User will input the v0 and angle in degree. Assume the ball start at (0,0).

Fill the spreadsheet for the position of the ball every 0.1 sec. Stop when the ball hit the ground.
I do have the function for calculating the posistion x and y. And the condition for the do while will be (y>=0) so that the loop stops calculating for both y and x once y < 0
But I am having trouble finding out how to arrange them into the spreadsheet.
For example,
first column will have all the time value (start at 0, increment of 0.1), the 2 columns next to it will have all corresponding values for x and y at that particular time.
Please help me with some hint or pointer. Thanks a lot.

Option Explicit

Sub problem2()
Sheets("Sheet2").Select
Dim y0 As Double, x0 As Double, v0 As Double
Dim ang As Double, g As Double, t As Double
Dim index_c As Integer, index_r As Integer, x As Double, y As Double
Range("f2").Select
v0 = ActiveCell.Value
Range("f3").Select
ang = ActiveCell.Value
g = -9.81
End Sub

Function ypos(y0, v0, ang, g, t) As Double
Dim ang_r As Double
ang_r = ang * (3.14159 / 180)
ypos = y0 + (v0 * t) * Sin(ang_r) + 0.5 * g * (t ^ 2)
End Function

Function xpos(x0, v0, ang, t) As Double
Dim ang_r As Double
ang_r = ang * (3.14159 / 180)
xpos = x0 + v0 * Cos(ang_r) * t
End Function