visual Queue in excel

[honeyoak]

Hi all, I am trying to create a macro that makes a visible queue from a dynamically changing cell (all values are integers). I am having a hard time with the logic structure.

for example, to use array notation ( just replace cells with the x's):

lets say that we have the following cells:

x[i,j] = 1
x[i+1,j] = 2
x[i+2,j] = 4
x[i+3,j] = 4
x[i+4,j] = 2

where x[i,j] changes dynamically. lets say that x[i,j] now equals 2 - the x[i+4,j]'s value is dropped ;

x[i,j] = 2
x[i+1,j] = 1
x[i+2,j] = 2
x[i+3,j] = 4
x[i+4,j] = 4

I want this to update every 5 seconds or so. my current code is a bit of a mess b

Sub loops()

Dim c1, c2 As Integer
c1 = 2
c2 = 2
Do While c1 <= 10
If Cells(c1, 1).Value <> Cells(c1 + 1, 1) Then
Cells(c1 + 1, 1).Value = Cells(c1, 1).Value
Cells(c1, 1).Value = Cells(c1 - 1, 1).Value

End If
c1 = c1 + 1
Loop

End Sub
thanks a bunch.

[Kenneth Hobs]

A looped application.ontime might work better.

Dim a() As Variant

Sub StartArrayShift()
Dim i As Integer
FillA1A4
a() = WorksheetFunction.Transpose(Range("A1:A4"))
Application.ScreenUpdating = True
For i = 1 To 5
ArrayShift
DoEvents
Application.Wait Now() + TimeValue("00:00:05")
Next i
End Sub

Sub ArrayShift()
Dim i As Integer, b() As Variant, x As Variant
a() = WorksheetFunction.Transpose(Range("A1:A4"))
x = a(UBound(a))
b() = a()
a(1) = x
For i = 2 To UBound(a)
a(i) = b(i - 1)
Next i
Range("A1:A4").Value2 = WorksheetFunction.Transpose(a)
End Sub

Sub FillA1A4()
'Add some dummy data for testing purposes
Range("A1").Value = 1
Range("A2").Value = 2
Range("A3").Value = 3
Range("A4").Value = 4
End Sub

[honeyoak]

Thanks A lot!!!