Copying a cell's value every X seconds into adjacent cell (+keep track of it)

[bbot23]

Hi guys,

I'm trying to have a kind of recorder that copies A1 into B1 every 15 seconds. The next time it copies A1 into B1 again, I want the previous B1 to go to C1. This cascade goes on until E1, where the next time the "copying" happens, E1 gets erased.

Basically after 60 seconds, A1:E1 are populated. After 75 seconds, A1 value goes to B1 (the copying), B1 value goes to C1, C1 value goes to D1, D1 value goes to E1, and E1 value is erased. There is from then on always 5 values in those 5 cells.

Here is an example of how the cells in row 1 would look like over time:

During 15 seconds:
1 2 3 4 5
2 2 3 4 5
8 2 3 4 5
7 2 3 4 5
5 2 3 4 5

After 15 seconds:

1 5 2 3 4
3 5 2 3 4
8 5 2 3 4
0 5 2 4 5

Etc.

Also, I will probably do this for the second row, third row, fourth row, etc. I don't know if it's important to know but A1, or the values in the first column, constantly change throughout the day.

Thanks in advance guys, you have always been very helpful.

 

[Greg Truby]

Good job describing the specifications.

<SUP>EDIT</SUP> shouldn't the second data set read:

X 1 2 3 4
X 2 2 3 4
X 8 2 3 4
X 7 2 3 4
X 5 2 3 4 <SUB>/EDIT</SUB>


What code have you written thus far? Are you simply stuck on the "every 15 seconds" bit? If so, have a look at the ONTIME statement in VBA. In addition to the help example there are oodles of examples here on the board.

 

[bbot23]

Good job describing the specifications.

<SUP>EDIT</SUP> shouldn't the second data set read:

X 1 2 3 4
X 2 2 3 4
X 8 2 3 4
X 7 2 3 4
X 5 2 3 4 <SUB>/EDIT</SUB>


What code have you written thus far? Are you simply stuck on the "every 15 seconds" bit? If so, have a look at the ONTIME statement in VBA. In addition to the help example there are oodles of examples here on the board.

Oh I apologize, I didn't explain it properly. The two examples are not data sets. It only shows the first row as it appears every few seconds, until the 15th second. The jumbled numbers (1 2 4 7 5) were just meant to show the changing nature of A1. This is what I meant:

During first 15s:

x 2 3 4 5
x 2 3 4 5
x 2 3 4 5
x 2 3 4 5
x 2 3 4 5

After 15s:

y x 2 3 4
y x 2 3 4
y x 2 3 4
y x 2 3 4
y x 2 3 4

After another 15s

z y x 2 3
z y x 2 3
z y x 2 3
z y x 2 3
z y x 2 3

Etc.

I didn't write any code for this. I'm not very literate with VBA but I've played around with it so I'll take a look at the ONTIME examples per your suggestion. Is that all I need?

 

[Greg Truby]

Well obviously, you need the code to run the transfers working first. Once you have that working properly then you can worry about the "every 15 seconds" bit.

When I was first learning VBA, I would first make sure I had my range reference correct and then I would go about making the changes. Let's say I need to start with a particular cell - like A2 - and then tranfer its contents to another cell - two rows down and three columns over. I'd paint the cells first and make sure I had the correct cells. Then I'd go about the rest. So my first set of code might look about like so:

Sub TestOne()
    Dim rngSource As Excel.Range, _
        rngTarget As Excel.Range
        
    Set rngSource = Range("A2")
    Set rngTarget = rngSource.Offset(2, 3)
    
    rngSource.Interior.Color = vbCyan
    rngTarget.Interior.Color = vbYellow
 
End Sub

Have a go at writing a bit of code that will at least set range object variables to the correct ranges.