# Excel: Dynamic plot with specific variable on x-axis

#### Lehman

##### New Member
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
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.
You can find the file here:
HTML:
``    https://filebin.net/mwdmkf3vzf175zqf``

#### Lehman

##### New Member
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 have updated the excel file here:
https://filebin.net/71opv5se062v9p35

#### offthelip

##### Well-known Member
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

#### Lehman

##### New Member
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
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

#### Lehman

##### New Member
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
No, not quite, this will do it:
Code:
``cells((lastrow+1,2)=cells(2,3)``