Check a cell for duplicates.

[Unknown_User]

With your help I have now managed to produce a really useful spreadshee
but I am being driven nuts by my inability to finish the final part!

Along with a bunch of other stuff my code creates a random number.

I have managed to find the next available cell in column A on a give
spreadsheet and place the number there.

What I want to do before entering the number is check that it doesn'
already exist somewhere in that column. If it is already present I wan
to loop until a unique one is found.

I have no doubt that this is really easy but I am just too dumb.....!

Thanks for your help

 

[Frank Kabel]

Hi
see: http://www.cpearson.com/excel/NoDupEntry.htm

 

[Unknown_User]

....sorry to be such a newbie here.

I have read the link, how do I use COUNTIF within my VB?

This seems to be a function within excel, I'm not sure how to access i
within the code.

Thanks

 

[Unknown_User]

...worked it out guys.

Private Sub CommandButton1_Click()

With Worksheets("Sheet1").Columns("A:A")
Do
rand = Rnd()
Set oCell = .Find(rand)
Loop Until oCell Is Nothing
Worksheets("Sheet1").Range("a1").Value = rand
End With

End Su