Creating values with loop in vba and storing them in a column in the worksheet?

Question

I am trying to calculate velocity and acceleration, with the variable being the time t. I'd like to write these in vba, to make it faster, because later on I'll need velocity and acceleration for several other calculations combined with different logical conditions. I would like the results to be printed in the worksheet, to double check during calculation if the results are realistic.

example of how it should like more or less

t        vel          a

0.002   39        -777
0.004   38.6      -802
0.006   35        -500
0.008   33.4      -400
0.01    32.1      -12297.1

So I have tried a few different things based on comments: This first code example works fine I think from the results, but I still can see any of the results in between > so main question: any chance I can change the commend to write to the worksheet without changing much else?

The second code example is an attempt to write everything into arrays: I do understand the principle I think, but here the main error seems to be that my variable t is not getting generated properly and therefore the formulas are not calculated: can't find the mistakes here and I would be grateful for some more help...

Kinematics Calculation

'Set t from 0 to 2 s with time step of 0.002s; Calculate Rot_vel until <= 0, 
'Find index t_2 when Rot_vel <= 0
t = 0
Do

t = t + 0.002

Rot_vel = -10356# * t ^ 6 + 24130# * t ^ 5 - 19002# * t ^ 4 + 4933# * t ^ 3 + 
362# * t ^ 2 - 213# * t + 39
    Worksheets("test").Range(Cells(3, 2), Cells(1003, 2)) = Rot_vel

Loop Until Rot_vel <= 0


If Rot_vel <= 0 Then

t_2 = t

    If t_2 > 0 Then
       Debug.Print t_2
    End If
 End If

'Continue calculations for t 0 to t_2 with 0.002 steps
t = 0
Do

t = t + 0.002

    A_rot = -62136# * t ^ 5 + 120650# * t ^ 4 - 76008# * t ^ 3 + 14797.8 * t 
 ^ 2 + 723.26 * t - 212.7                                                                                                    


    Worksheets("test").Range(Cells(3, 3), Cells(1003, 3)).value = A_rot

    L = MoI * Rot_vel / 1000
    M = MoI * A_rot / 1000

    Worksheets("test").Range(Cells(3, 8), Cells(1003, 8)).value = L
    Worksheets("test").Range(Cells(3, 9), Cells(1003, 9)).value = M

    G = L / t_2
    Worksheets("test").Range(Cells(3, 10), Cells(1003, 10)).value = G


 Loop Until t = t_2

Second version:

Kinematics Calculation

'Set t from 0 to 2 s with time step of 0.002s; Calculate Rot_vel until <= 0, 
'Find index t_2 when Rot_vel <= 0

Dim arrCalc As Variant
Dim i As Integer
Dim j As Integer

ReDim arrCalc(i To 1003, j To 13)

For i = LBound(arrCalc, 2) To UBound(arrCalc, 2)

t = 0
Do
t = t + 0.002

    arrCalc(i, 1) = t

    arrCalc(i, 2) = -10356# * t ^ 6 + 24130# * t ^ 5 - 19002# * t ^ 4 + 4933# 
  * t ^ 3 + 362# * t ^ 2 - 213# * t + 39                    'Rot_vel

Loop Until arrCalc(i, 2) < 0

Dim pos, val
val = 0
pos = Application.Match(val, arrCalc(i, 2), False)
pos = t_2


t = 0
Do
t = t + 0.002

    arrCalc(i, 3) = -62136# * t ^ 5 + 120650# * t ^ 4 - 76008# * t ^ 3 + 
    14797.8 * t ^ 2 + 723.26 * t - 212.7

    arrCalc(i, 8) = MoI * Rot_vel / 1000      'L                                                                                       


    arrCalc(i, 9) = MoI * A_rot / 1000    'M                                                                                        


    arrCalc(i, 10) = 1 / t_2 * L     'G                                                                                           


Loop Until t = t_2

Next i

With Worksheets("test")

    .Cells(2, "A") = 0
    .Cells(3, "A").Resize(UBound(arrCalc, 1), UBound(arrCalc, 2)) = Rot_vel
    .Cells(2, "A").Resize(UBound(arrCalc, 1) + 1, 1) = t
    '.Cells(3, "C").Resize(UBound(arrCalc, 1), UBound(arrCalc, 3)) = A_rot

End With

Answer 1

Build a 2-D array with times and calculations then dump the results back onto the worksheet.

Sequential time is very prone to 15 significant digit floating point errors. These errors can be minimized with a form of datum dimensioning that creates all new entries relative to the original starting point instead of the previous value. The former method can have no error greater than a single calculation while the latter can compounding errors by carrying them into the next calculation.

Sub kinematicsCalculation()
    Dim t As Double, start As Double, i As Long, arr As Variant

    start = TimeSerial(0, 0, 0)   'start at 0 second mark
    ReDim arr(1 To 1000, 1 To 2)

    For i = LBound(arr, 1) To UBound(arr, 1)
        arr(i, 1) = start + TimeSerial(0, 0, 2 * i) / 1000
        'cannot attempt velocity calc without Rot_vel_i
        arr(i, 2) = "<velocity calculation here>"
    Next i

    With Worksheets("sheet")
        .Cells(2, "B") = start
        .Cells(3, "B").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
        .Cells(2, "B").Resize(UBound(arr, 1) + 1, 1).NumberFormat = "[hh]:mm:ss.000"
    End With
End Sub

Answer 2

Your variables a_rot and rot_val don't look to me like arrays but normal variables. Therefore, only one value is stored in them and of course you get only one value as an output.

I see two options: 1) You write all of your values into an array and then copy the array to the sheet. 2) You write each calculation line by line to the sheet. Number 1) is much much faster.

A solution could look something like this:

ReDim Array (Lines, Columns)    
For each line
Array (line, Columns1) = Formula1
Array (line, Columns2) = Formula2
Array (line, Columns3) = Formula3         
Next