excel 2003 - vba - swap cell content

[silentbuddha]

Hi,

I am stuck on this particular problem...

Scenario: I have a simple column in my activeworksheet

Column A
---------
1 ( Cell 1,1 )
2 ( Cell 2,1 )
3 ( Cell 3,1 )
4 ( Cell 4,1 )
5 ( Cell 5,1 )

let us suppose the user changes the content in Cell(1,1) from 1 to 2 and now the column would look like :

Column A
---------
2 ( Cell 1,1 )
2 ( Cell 2,1 )
3 ( Cell 3,1 )
4 ( Cell 4,1 )
5 ( Cell 5,1 )

Question : how do I use vba to dynamically change Cell(2,1) content from 2 to 1 in order to keep all values in column A unique ???

Thanks !

 

[9ballpimp]

What is your complete range? Is it only these 5?

Troy

 

[pbornemeier]

What should happen if Cell(1,1) is changed to 87
What should happen if all cells are changed to 1 at once?

 

[silentbuddha]

Hi 9ballpimp,

My column range is fixed at these 5 values

Hi pbornemeier,

if the user changes the cell content to 87 or if the cells are all changed to 1 then do nothing....

Thank you both for being patient with me

 

[9ballpimp]

So they only have to be unique, not continuous, correct?

Troy

 

[9ballpimp]

How about using data validation to do this.

In my test, I'm using cells J2:J10. In these cells, add a custom data validation, and in the formula box, add:

=IF(SUM(IF(FREQUENCY($J$2:$J$10,$J$2:$J$10)>0,1))>1,TRUE,FALSE

Now, add 1 in J2, then try to type 1 in J4. It won't let you, and keeps the same value.

Troy

 

[silentbuddha]

Hi Troy,

My apologies if my orignal question was not clear....just for simplicity and not taking into consideration error message or uniqueness.

I will present the scenario in another fashion...

Column A
---------
1 ( Cell 1,1 )
2 ( Cell 2,1 )
3 ( Cell 3,1 )
4 ( Cell 4,1 )
5 ( Cell 5,1 )

event 1 : user will change the value in cell(1,1) from 1 to 2
event 2 : vba code will remember old value in cel(1,1) ..which was 1
event 3 :vba code will then check column A to see if there is another cell with the same value
event 4 : if vba code finds a cell with same value (excluding the cell where the change occured), vba code will replace the new cell with the old value
event 5 : exit sub

I hope this clarfies what I am searching for....Thank you for your understanding

Keith

 

[9ballpimp]

Got it. Using data validation, though, accomplishes exactly that. If it's already in the list, then it doesn't allow the entry, and reverts back to the previous value.

Why is this different? Maybe I'm not getting something...

Troy

 

[silentbuddha]

Hi Troy,

data validation would work if I want to restrict the user with regards to your explanation. However, what I am still looking for is guidance on how to do a swap between 2 cell contents based on my example.

Column A
---------
1 ( Cell 1,1 )
2 ( Cell 2,1 )
3 ( Cell 3,1 )
4 ( Cell 4,1 )
5 ( Cell 5,1 )

event 1 : user will change the value in cell(1,1) from 1 to 2
event 2 : vba code will remember old value in cell(1,1) ..which was 1
event 3 : vba code will then check column A to see if there is another cell with the same value
event 4 : if vba code finds a cell with same value (excluding the cell where the change occured), vba code will replace the new cell with the old value
event 5 : exit sub

Thanks once again

 

[9ballpimp]

I gotcha. I felt the "click" in the ole noggin this morning. Just needed some sleep. Let me work on this a bit, and I'll post back.

Troy