Excel VBA Algorithm

[Rad]

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.






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.

Here is an 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

 

[Clif McIrvin]

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.






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.

Here is an 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

Thinking out loud:

Dim PreviousValue(1 to 30) as Integer
Dim myLoBound as Integer
Dim myHiBound as Integer
Dim newValue as Integer

In the WorkBook Open Event, copy the 30 column values into
PreviousValue.

In the WorkSheet Change Event, scan the column looking for the first
value that is different. Set myLoBound to the index pointer into the
array. Set newValue to the new value entered by the user. Continue the
scan, looking for the original location of the new value. Set myHiBound
to the index pointer into the array.

For i = myLoBound + 1 to myHiBound
PreviousValue(i) = PreviousValue(i-1)
Next i
PreviousValue(myLoBound) = newValue

Copy PreviousValue into the column.

--------------------

While writing the above, it occurred to me that the worksheet change
event supplies a range object
(from the help file
Occurs when cells on the worksheet are changed by the user or by an
external link.

Private Sub Worksheet_Change(ByVal Target As Range)

Target The changed range. Can be more than one cell.

You should be able to use the Target object both to determine if the
change was in your area of interest, and to identify myLoBound.

HTH