Excel: Dynamic plot with specific variable on x-axis

Lehman

New Member
Joined
Oct 5, 2019
Messages
13
I have a variable Y


Code:
    Y = RANDBETWEEN(2,15)
And a variable X


Code:
    X = NOW()
I am using the following VBA code to refresh the date; to simulate a real time data flow:


Code:
    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:
HTML:
    https://filebin.net/mwdmkf3vzf175zqf
 

Lehman

New Member
Joined
Oct 5, 2019
Messages
13
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
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.
Code:
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
 
Last edited:

Lehman

New Member
Joined
Oct 5, 2019
Messages
13
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.
Code:
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:
Code:
C2 = RANDBETWEEN(2,15)
And then the following loop:
Code:
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
try this code, Note I haven't used C2 because it isn't necessary but you easly could use it if you want.
Code:
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
 
Last edited:

Lehman

New Member
Joined
Oct 5, 2019
Messages
13
try this code, Note I haven't used C2 because it isn't necessary but you easly could use it if you want.
Code:
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;
Code:
Cells(last row + 1,2) = Application.WorksheetFunction.RandBetween(2, 15)
Should be the following:
Code:
Cells(2, 2) = Application.ActiveSheet.Range("C2").Select
I am really sorry for the noob questions!
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
No, not quite, this will do it:
Code:
cells((lastrow+1,2)=cells(2,3)
 

Forum statistics

Threads
1,085,419
Messages
5,383,552
Members
401,836
Latest member
Bweston07

Some videos you may like

This Week's Hot Topics

Top