# jindonExcel Samurai

• Member since Jul 19th 2004
• Last Activity:
Posts
9,398
3
Points
47,063
Profile Hits
9,862
• Hi

Preventing Duplicate Entries With VBA Code

and would like to ask you for some help.

The column A in my worksheet has already a validation rule to check value between 10000-99999 (only for 5 digits positive integers).

Your code which has worked fine so far that the user cannot enter double numbers in the column A.

But if the user put < 5 or > 5 digits number or combine number with coma like 12, etc. the rule of validation is inefficient and doesn’t work properly.

Any Idea what to change?

thx

tom

• Hi, i came across your codes (sorting alphanumeric data ignoring numbers) below :

1. Sub test()
2. Dim a, x, i
3. a = Range("a1").CurrentRegion.Value
4. ReDim Preserve a(1 To UBound(a, 1), 1 To 2)
5. For i = LBound(a, 1) To UBound(a, 1)
6. x = Val(a(i, 1))
7. x = Replace(a(i, 1), x, vbNullString)
8. a(i, 2) = UCase(Trim(x))
9. Next
10. QuicksortA a, LBound(a, 1), UBound(a, 1), 2
11. Range("a1").Resize(UBound(a, 1)) = a
12. End Sub
13. Sub QuicksortA(ary, LB, UB, ref)
14. Dim M As Variant, temp
15. Dim i As Long, ii As Long, iii As Integer
16. i = UB
17. ii = LB
18. M = ary(Int((LB + UB) / 2), ref)
19. Do While ii <= i
20. Do While ary(ii, ref) < M
21. ii = ii + 1
22. Loop
23. Do While ary(i, ref) > M
24. i = i - 1
25. Loop
26. If ii <= i Then
27. For iii = LBound(ary, 2) To UBound(ary, 2)
28. temp = ary(ii, iii): ary(ii, iii) = ary(i, iii)
29. ary(i, iii) = temp
30. Next
31. ii = ii + 1: i = i - 1
32. End If
33. Loop
34. If LB < i Then QuicksortA ary, LB, i, ref
35. If ii < UB Then QuicksortA ary, ii, UB, ref
36. End Sub

I try using this code but the data that you make this code for doesn't have any header. Can you insert a header in it? I really need to use this code.