Need simple VBA code function that gets some values/cells and initiate them in a different order

[dikken20]

Hello,

The Function I need (I need this as a function not a Sub, as I would like to run it using an If statement) is as the following example:

A1 = 33 ; B1 = 5 ; C1 = 10 ; D1 = 7 E1 = 22

The function will get the entire range's values and will initiate them as the following:
A1 will remain the same - NO need to initiate it at all!
B1 = A1 (33)
C1 = B1 (5)
D1 = C1 (10)
E1 = D1 (7)


This is just an example to illustrate the point, but here are some important rules/facts needed to take on account:
1. The function will need to get different number of cells (in this example the input is only 5 cells but sometimes it will be 10 cells, sometimes 2, sometimes 100..etc,..)
2. The Cells are NOT located at the same location not located at the same order. Meaning, It could be T125, U125, V125 and also can be F33, I39, K108, M59.

 

[dikken20]

The only way I know of is to use a sub & run it as & when needed.

OK.
I would much appreciate that if you could write some code for me please so I can check how I can implement it in my sheet.

 

[Fluff]

How would you tell the macro which cells to look at, if they are constantly changing?

 

[dikken20]

How would you tell the macro which cells to look at, if they are constantly changing?

This is the logic behind the scenes:

The column "Deals" represents the number of deals in a certain time (1 minute) and changed dynamically using 3rd party data source.
I do have a dynamic cell that shows the exact time every second (automatic clock).
Basically what I need is that every certain time (in this example 1 minute, but can be shorter or longer), it will copy the Deals column to the right. Needless to say that in order to prevent from the other columns ( Deals -3, Deals -2, Deals -1) data lost it will copy it first, meaning, Deals -2 will be copied to Deals -3, Deals -1 to Deals -2, Deals 0 to Deals -1 and finally Deals to Deals 0.

In the original question I asked to copy cells in order to be more flexible but the end purpose is to get the entire column copied.
Also I thought of doing this automatically using the Dynamic clock cell every X seconds without any manual work, but you said it is impossible so I guess I have only the Sub option.

Does that make any sense?

Deals	Deals 0	Deals -1	Deal -2	Deals -3
5	19	33	13	99
10	17	232	39	30
33	44	200	29	81
18	3	9	0	21

​

 

[Fluff]

As I have never user timers within code, I cannot help on this.

 

[dikken20]

As I have never user timers within code, I cannot help on this.

Apology I think you got me wrong.
My main purpose was to run a function at a certain time based on the clock, but since you said that it won't work - I remained with the Sub option to run it manually and this is the code I ask to have, regardless to the clock/timer inside the code..

 

[Fluff]

A UDF is just the same as an Excel function, so when the data changes the cells with the function would also change to reflect the new data.
This will paste the data in col A into a new col B

Sub dikken()
   Range("B:B").Insert
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Offset(, 1).Value = .Value
   End With
End Sub