dynamic data

  • Thread starter Thread starter andrew21
  • Start date Start date
A

andrew21

Hi..i have numeric data being automatically updated second by second i
a particular cell...how would i be able to save each "update" in a ne
cell either below the dynamic cell or in an adjacent column...ideall
with the time of entry in another column...the purpose being t
eventually be able to chart this data set...thanks all for any pointer
you may have
andre
 
A better answer would depend on how you are doing the
updating. Generally speaking, this statement will enter a
value in the next cell below a range of data:

'Assuming your range is in Column A
'Each time you run this code, MyValue will be added to the
'column.
If ActiveSheet.Range("A65536").end(xlup).row < 65536 then
ActiveSheet.Range("A65536").end(xlup).offset(1,0) =
MyValue
End if

tod
 
thanks for your reply Tod...the cell in question is a pasted cell fro
another workbook..it is a formula which is doing basic calculations o
a range of cells in the other workbook which are constantly updatin
thru a DDE link...i assumed that in order to create a dynamic chart
would need some time values to plot along with the cell value...is thi
true?...that's the only reason i need the time values...basically tryin
to create a 1 minute bar chart based on the changing values in thi
cell...also i have no VBA experience...where would i place the scrip
you provided
 
Sorry. I remembered after I sent my reply that I was in
the Worksheet Functions group.

I'm not sure of your exact objective, but I'm going to
take a guess that this is what you want, and then you can
tinker with it. Assuming that the always-updating value is
in A1, setup two columns for entering the value and the
time of the value. Let's use Column C and D. So in C1 put
Time (or something similar) and in D1 put a title for the
value.

To enter the code, go to the workbook where the value is
being entered and click Alt+F11. This will take you to the
VB Editor. You should see a blank white screen amid all of
the windows. (If you don't then click F7 to bring it up.)
This is where your code will be placed.

In that VB Editor screen, there is a small window that
shows all of the open projects. It usually
says 'VBAProject' in it's banner. One of those projects is
your workbook name. Look for that workbook and then find
the sheet name. Double-click the sheet name to bring up
the code page for that sheet. Copy and paste this code
into that code page:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
ActiveSheet.Range("C65536").End(xlUp).Offset(1,
0).Value = Time
'You could also replace 'Time' with 'Now' to get
the date and time stamp
ActiveSheet.Range("C65536").End(xlUp).Offset(0,
1).Value = ActiveSheet.Range("A1").Value
Application.EnableEvents = True
End Sub

This code will run whenever any cell in the sheet requires
the sheet to be calculated. It will then put the value of
A1 in the list in column D and the time next to it in
Column C. Then you can use columns c and d to make your
chart.

tod
 
Back
Top