Hi Rick,

No problems, my bad too.

Using a dynamic VBA array will scan the column at the start of the run, this
way it will pick up the latest changes to the numbers in the column.

The number 9 is sat from the first change in the second column in the
example; the third column is another change to the column. When the array
starts its run at the very beginning of the run, it will read and create an
array with the new numbers.


Rad

"Rick Rothstein" wrote:

I'm sorry, but I didn't scroll your original message down to see the
examples you provided (the gap you left between your "Thank you, Rad" and
the examples was such that the examples were out of view and I didn't think
to try and scroll down because of the large amount of white space under your
signature). Anyway, I have a question about your "Changed Again" example...
why didn't the 9 move down when the 10 was typed above it? Was that because
the user typed it, so it needed to stay in the slot the user picked for it?
If that is the case, for how long does the code need to remember that.. just
until the worksheet is closed, or forever? Or did you make a mistake and the
9 should have slid down too?

--
Rick (MVP - Excel)


"Rad" wrote in message
...
First, thank you for your response.
I have posted the three examples to show how the numbers will change. If
you input in number 9 in number 5 cell, yes 5 will be replaced by the
number
9, then after number 9, there will be 5,6,7,8,10.
In regard to 30, I will have validation code to not accept any number
above
number 30.
Thank you,
Rad


"Rick Rothstein" wrote:

Well, you made the task even more challenging by not giving us a clue as
to
how you want any duplicates removed... the simplest would be to trade the
number being overwritten for the number the user is typing in... would
that
be acceptable or did you have some other way in mind? Also... what cells
are
your numbers in (or what cells are they allowed to be in? Do you have any
controls to prevent the user from typing in more than 30 numbers in your
column (that would create an impossible situation to handle other than
removing the entry immediately)? Do you have any control to stop the user
from typing in a number bigger than 30?

--
Rick (MVP - Excel)


"Rad" wrote in message
...
Hi,

I am looking for an advice on this challenging algorithm.

I have a column in ms excel sheet and has the numbers from 1 €“ 30, and
I
need to use vba code to allow the user to be able to change a number at
a
time. Then as soon as it is changed, the column numbers need the
change
so
there is no duplicate numbers in the column. In other words, the
column
numbers 1- 30 must have only a number of each.

Any advice will be appreciated.

Thank you,
Rad





I thought about swapping, but the number next to the changed number
must
be
reduced by a number until the number that has been changed from is
reached.

For example:
Original Changed Changed Again
1 1 1
2 2 2
3 3 10
4 4 3
5 9 9
6 5 4
7 6 5
8 7 6
9 8 7
10 10 8