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 came across your codes (sorting alphanumeric data ignoring numbers) below :
- Sub test()
- Dim a, x, i
- a = Range("a1").CurrentRegion.Value
- ReDim Preserve a(1 To UBound(a, 1), 1 To 2)
- For i = LBound(a, 1) To UBound(a, 1)
- x = Val(a(i, 1))
- x = Replace(a(i, 1), x, vbNullString)
- a(i, 2) = UCase(Trim(x))
- Next
- QuicksortA a, LBound(a, 1), UBound(a, 1), 2
- Range("a1").Resize(UBound(a, 1)) = a
- End Sub
- Sub QuicksortA(ary, LB, UB, ref)
- Dim M As Variant, temp
- Dim i As Long, ii As Long, iii As Integer
- i = UB
- ii = LB
- M = ary(Int((LB + UB) / 2), ref)
- Do While ii <= i
- Do While ary(ii, ref) < M
- ii = ii + 1
- Loop
- Do While ary(i, ref) > M
- i = i - 1
- Loop
- If ii <= i Then
- For iii = LBound(ary, 2) To UBound(ary, 2)
- temp = ary(ii, iii): ary(ii, iii) = ary(i, iii)
- ary(i, iii) = temp
- Next
- ii = ii + 1: i = i - 1
- End If
- Loop
- If LB < i Then QuicksortA ary, LB, i, ref
- If ii < UB Then QuicksortA ary, ii, UB, ref
- 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.
ExUser
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