Values for charts

[Jeff Ciaccio]

I have a VBA project that generates cell values, and if I then select those
values, I can create a chart. However, when I run the procedure again,
there is only a single point on the chart. I would like the charts to
update automatically. Can anybody help me out with that?

I have two charts, both use X values from D2202. One chart has Y values
from E2:E202, and the other has Y's from F2:F202. All of those values are
set with my procedure, and all of those are cleared before the procedure is
run again. See code below.

Thanks!!

Option Explicit
' Sub Worksheet_Change(ByVal Target As Range) ' this will trigger if any
part of the sheet is changed
Sub One_D_Motion()
Dim t As Single
Dim time1 As Single
Dim vInit1 As Single
Dim acc1 As Single
Dim xInit1 As Integer
Dim acc2 As Single
Dim x As Single
Dim vel As Single
Dim row As Integer
Dim startingPos As Integer
Dim tInit As Single

' This will find the position and velcoity for the first 20 seconds
' The user must set the initial conditions and the time period for the
accelerations
' Written 6/12/2008 by Jeff Ciaccio

' Clear out the range where the data will go
Range("D2:F202").Delete


' Record the values the user has chosen
time1 = Range("b4").Value
vInit1 = Range("b2").Value
startingPos = vInit1
acc1 = Range("b3").Value
xInit1 = Range("b1").Value
acc2 = Range("b6").Value

' Set the first row cells
t = 0
row = 2
Range("D2").Value = 0
Range("E2").Value = vInit1
Range("f2").Value = acc1
Range("d3").Select

Do Until t = time1
t = Round(t + 0.1, 1)
row = row + 1
x = xInit1 + vInit1 * t + 1 / 2 * acc1 * t ^ 2
vel = vInit1 + t * acc1
Range("d" & row).Value = t
Range("e" & row).Value = x
Range("f" & row).Value = vel
Range("g" & row).Value = acc1
Loop
MsgBox prompt:="Time period 1: " & vbLf & "The ending position was " & x & "
meters, and the starting position was " & xInit1 & ", so the displacement
was " & (x - xInit1) & " meters", Title:="End of first period"
MsgBox prompt:="Time period 1: " & vbLf & "The ending velocity was " & vel &
" m/s, and the starting velocity was " & vInit1 & ", so the Delta v was " &
(vel - vInit1) & " m/s", Title:="End of first period"
MsgBox ("Time period 1: " & vbLf & "Since there was constant acceleration,
the average velocity is:" & vbLf _
& "avg vel = (" & vInit1 & " + " & vel & ") " & Chr(247) & "2 = " &
(vInit1 + vel) / 2) & " m/s", Title:="End of first period"
xInit1 = x
vInit1 = vel
tInit = t
Do Until t = 20
t = Round(t + 0.1, 1)
row = row + 1
x = xInit1 + vInit1 * (t - tInit) + 1 / 2 * acc2 * (t - tInit) ^ 2
vel = vInit1 + (t - tInit) * acc2
Range("d" & row).Value = t
Range("e" & row).Value = x
Range("f" & row).Value = vel
Range("g" & row).Value = acc2
Loop
MsgBox prompt:="Time period 2: " & vbLf & "NOTE: To find the average
velocity for the whole period, you CANNOT simply average the starting and
ending velocities!" _
& vbLf & "Just find the displacement and divide by 20.0 seconds." & vbLf
_
& "The average velocity was " & (x - startingPos) / 20 & "m/s^2",
Title:="End of simulation"




End Sub

 

[Jon Peltier]

Hmm, Before reading through dozens of lines of someone else's code (I can't
even understand my own a month after I wrote it), I wonder whether you've
checked the chart's data.

First of all, the series formula. Does it look like this:
=Series(Sheet1!$E$1,Sheet1!$D$2:$D$202,Sheet1!$E$2:$E$202,1)
Are the expected ranges in the second and third positions? Do the ranges
contain the expected data? Is the data numerical, or has it somehow
converted to text?

More fundamental: Why not use worksheet formulas for this?

- Jon

 

[Jon Peltier]

More fundamental: Why not use worksheet formulas for this?

I've emailed a worksheet formula based version of your physics demo. I'm
going to add it to a blog post sometime soon.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

 

[Jeff Ciaccio]

Where do we find your blog?

Thanks!

I've emailed a worksheet formula based version of your physics demo. I'm
going to add it to a blog post sometime soon.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

 

[Jon Peltier]

http://peltiertech.com/WordPress/

- Jon