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.

 

[Special-K99]

This is probably beyond my VBA skills but this may help others who want to solve this.

What does "initiate" mean?

It looks as though you're simply moving the cells one to the right from the 2nd cell onwards, ie B1=A1, C1=B1, D1=C1, E1=D1
But in the 2nd rule your cells are not consecutive (column F then column I, column K, column M)

1st question, how are you going to indicate to the function what cells should be changed? Multiple selects on the spreadsheet or a list of cells to be altered or some other way?

 

[dikken20]

This is probably beyond my VBA skills but this may help others who want to solve this.

What does "initiate" mean?

It looks as though you're simply moving the cells one to the right from the 2nd cell onwards, ie B1=A1, C1=B1, D1=C1, E1=D1
But in the 2nd rule your cells are not consecutive (column F then column I, column K, column M)

1st question, how are you going to indicate to the function what cells should be changed? Multiple selects on the spreadsheet or a list of cells to be altered or some other way?

Good point about Rule #2, so let me change it in order to make it simple and in order yo give you the chance to help me on solve this

Rule #2 - First variable (A1) in the function can be ANY cell (since this cell will not be changed but only "copied" somewhere else), WHILE the other cells' order WILL be consecutive ONLY, for instance:

function(B55, D10,E10,F10) {result: D10 = B55, E10 = D10, F10 = E10)
function(A831, R122, S122, T122, U122, V122, W122, X122) {result: R122 = A831, S122 = R122, T122= S122, U122 = T122, V122 =U122, W12 = V122, X122 = W122)


Does that make any sense now?

 

[Special-K99]

You want a function with 100+ parameters?

This is beyond my VBA skills I'm afraid

 

[Fluff]

A UDF can only affect the cell(s) it's in, so what your asking is not possible.

 

[dikken20]

You want a function with 100+ parameters?

This is beyond my VBA skills I'm afraid

I can do with 50 as well..

 

[dikken20]

A UDF can only affect the cell(s) it's in, so what your asking is not possible.

OK but UDF can call a procedure (Sub) that will do that, right? In that case I won't mind a Sub doing that..

Also, What about just "adding" a cell so that all cells will move one cell to the right? for instance:
A1 = variable that is copied, while adding a cell in B1 that will move C1..D1..Etc,.. one cell to the right. Is that be possible?

 

[Fluff]

OK but UDF can call a procedure (Sub) that will do that, right?

Nope, because it's still a UDF.
If you had function(B55, D10,E10,F10) the function would have to be in D10,E10 & F10 & so those cells would not have a value of there own & would end up just showing what's in B55.

 

[dikken20]

Nope, because it's still a UDF.
If you had function(B55, D10,E10,F10) the function would have to be in D10,E10 & F10 & so those cells would not have a value of there own & would end up just showing what's in B55.

I don't want a Sub/macro that will need to run all the way so I could not keep working on the sheet, I need it to run at the background while I keep working..
So, how can I do this?

 

[Fluff]

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