jindon Excel Samurai

Hi

I used your code:

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
17. Sub QuicksortA(ary, LB, UB, ref)
18. Dim M As Variant, temp
19. Dim i As Long, ii As Long, iii As Integer
20. i = UB
21. ii = LB
22. M = ary(Int((LB + UB) / 2), ref)
23. Do While ii <= i
24. Do While ary(ii, ref) < M
25. ii = ii + 1
26. Loop
27. Do While ary(i, ref) > M
28. i = i - 1
29. Loop
30. If ii <= i Then
31. For iii = LBound(ary, 2) To UBound(ary, 2)
32. temp = ary(ii, iii): ary(ii, iii) = ary(i, iii)
33. ary(i, iii) = temp
34. Next
35. ii = ii + 1: i = i - 1
36. End If
37. Loop
38. If LB < i Then QuicksortA ary, LB, i, ref
39. If ii < UB Then QuicksortA ary, ii, UB, ref
40. 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.