Excel: Dynamic plot with specific variable on x-axis

[Lehman]

I have a variable Y

    Y = RANDBETWEEN(2,15)

And a variable X

    X = NOW()

I am using the following VBA code to refresh the date; to simulate a real time data flow:

    Sub Calculate_range()
    Range("A1:A7").Calculate
    Application.OnTime DateAdd("s", 2, Now), "Calculate_range"
    End Sub

So what I want is that the X variable will be on the X axis and the Y variable on the Y axis as well and then continuous between 10:00 until 14:00.


I have managed to do somehow with more variables however I just need one.
How do I go about this?
You can find the file here:

    https://filebin.net/mwdmkf3vzf175zqf

 

[Lehman]

Quite similar:
https://www.youtube.com/watch?v=xLTEnd1u5Eo&feature=youtu.be

However, I have just one cell, let's say A1 that is continuously refreshed every minute.

 

[Lehman]

I am pursuing the following method: https://filebin.net/71opv5se062v9p35

So I have a column A1: Time and Column B1: Value.
Now, I need to increase every minute A1 with a formula like the following:
=A2 + "00:01:00"

And simultaneously the B column. Lets say C1 gives the value =RANDBETWEEN(2;15). Then every time I increase a minute value, I am calling on C1 to give me the value in the B column as well to plot.
I want to do this in the range of 0900:1400. How do I go about this?
I have updated the excel file here:
https://filebin.net/71opv5se062v9p35

 

[offthelip]

I am not entirely clear what you are trying to do but this code shows you how to generate all the minutes between 9am and 2pm with a random number next to them.

Sub test()
Dim outarr() As Variant


min1 = 1 / (24 * 60)
loopcnt = 1 + 60 * 5
ReDim outarr(1 To loopcnt, 1 To 2)
tt = TimeSerial(9, 0, 0)
For i = 1 To loopcnt
 outarr(i, 1) = tt
 outarr(i, 2) = Application.WorksheetFunction.RandBetween(2, 15)
 tt = tt + min1
Next i
Range(Cells(1, 1), Cells(loopcnt, 2)) = outarr


End Sub

 

[Lehman]

I am not entirely clear what you are trying to do but this code shows you how to generate all the minutes between 9am and 2pm with a random number next to them.

Sub test()
Dim outarr() As Variant


min1 = 1 / (24 * 60)
loopcnt = 1 + 60 * 5
ReDim outarr(1 To loopcnt, 1 To 2)
tt = TimeSerial(9, 0, 0)
For i = 1 To loopcnt
 outarr(i, 1) = tt
 outarr(i, 2) = Application.WorksheetFunction.RandBetween(2, 15)
 tt = tt + min1
Next i
Range(Cells(1, 1), Cells(loopcnt, 2)) = outarr


End Sub

Hey, thanks for your reply! However I think if you can show me some code that appends a table of list every minute I can get this project done.
If you define cell C2 as the following:

C2 = RANDBETWEEN(2,15)

And then the following loop:

For every whole minute in range [9AM:2PM]
  Append the whole minute in cell A2
  Append the random number stated in C2 in B2

For the next whole minute in range [9AM:2PM]
  Append the whole minute in cell A3
  Append the random number stated in C2 in B3

For the next whole minute in range [9AM:2PM]
  Append the whole minute in cell A4
  Append the random number stated in C2 in B4

So at the end of the range 9AM:2PM we have a table of
5 Hours * 60 minutes = 300 Rows.
What I then when every time the loop is called upon, the graph is updated.

Can you let me know if this is possible?

 

[offthelip]

try this code, Note I haven't used C2 because it isn't necessary but you easly could use it if you want.

Sub test()
min1 = 1 / (24 * 60)
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If lastrow < 2 Then
Cells(2, 1) = TimeSerial(9, 0, 0)
Cells(2, 2) = Application.WorksheetFunction.RandBetween(2, 15)
Else
Cells(lastrow + 1, 1) = Cells(lastrow, 1) + min1
Cells(lastrow + 1, 2) = Application.WorksheetFunction.RandBetween(2, 15)
End If
Application.OnTime Now() + min1, "test"




End Sub

 

[Lehman]

try this code, Note I haven't used C2 because it isn't necessary but you easly could use it if you want.

Sub test()
min1 = 1 / (24 * 60)
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If lastrow < 2 Then
Cells(2, 1) = TimeSerial(9, 0, 0)
Cells(2, 2) = Application.WorksheetFunction.RandBetween(2, 15)
Else
Cells(lastrow + 1, 1) = Cells(lastrow, 1) + min1
Cells(lastrow + 1, 2) = Application.WorksheetFunction.RandBetween(2, 15)
End If
Application.OnTime Now() + min1, "test"




End Sub

Wauw this is exactly what I want!
If I want to pull the variable from C2;

Cells(last row + 1,2) = Application.WorksheetFunction.RandBetween(2, 15)

Should be the following:

Cells(2, 2) = Application.ActiveSheet.Range("C2").Select

I am really sorry for the noob questions!

 

[offthelip]

No, not quite, this will do it:

cells((lastrow+1,2)=cells(2,3)