jindon Excel Samurai

  • Member since Jul 19th 2004
  • Last Activity:
Posts
9,398
Reactions Received
3
Points
47,063
Profile Hits
9,862
  • 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
    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.