search and replace repeat values in a column? 1

[LamerThanU]

ok i have 1 column say "A" that is about 50 rows long and each cell has to be a unique number say 1 to 50.

if there is a repeat number somewhere in the column how can i, using a button, search down repeats and make the value the next largest number in the column (eg. 51) then keep searching for repeats to ensure i have unique id numbers for each row of data?

thank you in advance for your time.

 

[PHV]

And what have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IknowMe]

For x = 1 to 50
Worksheets("YourSheet").Cells(x,1).value = x
Next

No repeats.

Seriously, put a little more effort into your explanation as suggested by PHV and you'll get more effort in way of response.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

Search this Forum with the Keyword 'duplicate'.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[VulcanJedi]

what i like to do sometimes is use the advanced filter and check unique records only....

Tranpkp

 

[LamerThanU]

thing is i just recently became interested in vba so i dont have much to build on and honestly didnt even know where to start. obviously my programming skills are next to none hence my handle. in any case thankyou for the replies and in the future ill try a little more extensive research before posting a thread.

take care.

 

[IknowMe]

I hope I haven't caused any offense with an unintended tone in my reply. After re-reading my post I can see that the word "seriously" could come of differently than I intended. I started by making a comical poke at your OP, so I used the word seriously to let you know my intent to help was sincere. But I need more to go on before I can attempt to help.


To help you further....

How are the rows getting populated?
How are you getting duplicate values to begin with?

I'm slightly confused by

a unique number say 1 to 50
the next largest number in the column (eg. 51)

I know your trying to simplify the problem for times sake, but sometimes important clues are missed by lack of a complete disclosure of a work need.

Give us a little more to work with.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[LamerThanU]

actually you were very close with what you posted earlier. the only difference would be that it would find a duplicate value in the column and replace it with the next logical number in the column eg. the first column would represent before the search and the second column after...

51 51
52 52
53 53
53 57 <--- changed from 53 to 57
54 54
55 55
56 56

i hope this makes sence. regarding any offence from earlier... none taken. take care

 

[LamerThanU]

oh sorry to answer your questions

the rows would have been populated manually but your last reply with code gave me an idea on a different way... originally it would have been manual entry and user error would have been the cause for duplication.

thing is its not me using this spreadsheet in the end and im trying to make it easy.

 

[IknowMe]

Were you able to work something out or do you still need help?

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.