[COLOR=Navy]Sub[/COLOR] MG05Apr53
[COLOR=Navy]Dim[/COLOR] Ray
[COLOR=Navy]Dim[/COLOR] i [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] j [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] jj [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] ii [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] Temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
Ray = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] i = 1 To UBound(Ray)
[COLOR=Navy]For[/COLOR] j = i To UBound(Ray)
jj = IIf(IsNumeric(Mid(Ray(j, 1), 2, 1)), 2, 3)
ii = IIf(IsNumeric(Mid(Ray(i, 1), 2, 1)), 2, 3)
[COLOR=Navy]If[/COLOR] Val(Mid(Ray(j, 1), jj)) < Val(Mid(Ray(i, 1), ii)) [COLOR=Navy]Then[/COLOR]
Temp = Ray(i, 1)
Ray(i, 1) = Ray(j, 1)
Ray(j, 1) = Temp
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] j
[COLOR=Navy]Next[/COLOR] i
Range("B1").Resize(UBound(Ray)) = Ray
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Excel 2007 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Before Code | After Code | Should Be | ||
2 | C1 | C1 | C1 | ||
3 | C10 | CB1 | C2 | ||
4 | C11 | C2 | C3 | ||
5 | C12 | CB2 | C4 | ||
6 | C13 | C3 | C5 | ||
7 | C14 | CB3 | C6 | ||
8 | C15 | C4 | C7 | ||
9 | C16 | CB4 | C8 | ||
10 | C17 | C5 | C9 | ||
11 | C18 | CB5 | C10 | ||
12 | C19 | C6 | C11 | ||
13 | C2 | CB6 | C12 | ||
14 | C3 | C7 | C13 | ||
15 | C4 | CB7 | C14 | ||
16 | C5 | C8 | C15 | ||
17 | C6 | CB8 | C16 | ||
18 | C7 | C9 | C17 | ||
19 | C8 | CB9 | C18 | ||
20 | C9 | C10 | C19 | ||
21 | CB1 | CB10 | CB1 | ||
22 | CB10 | C11 | CB2 | ||
23 | CB11 | CB11 | CB3 | ||
24 | CB12 | C12 | CB4 | ||
25 | CB13 | CB12 | CB5 | ||
26 | CB14 | C13 | CB6 | ||
27 | CB15 | CB13 | CB7 | ||
28 | CB16 | C14 | CB8 | ||
29 | CB17 | CB14 | CB9 | ||
30 | CB18 | C15 | CB10 | ||
31 | CB19 | CB15 | CB11 | ||
32 | CB2 | C16 | CB12 | ||
33 | CB20 | CB16 | CB13 | ||
34 | CB3 | C17 | CB14 | ||
35 | CB4 | CB17 | CB15 | ||
36 | CB5 | C18 | CB16 | ||
37 | CB6 | CB18 | CB17 | ||
38 | CB7 | C19 | CB18 | ||
39 | CB8 | CB19 | CB19 | ||
40 | CB9 | CB20 | CB20 | ||
Sheet3 |
[COLOR="Navy"]Sub[/COLOR] MG06Apr30
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] jj [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ii [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, Lst)
[COLOR="Navy"]For[/COLOR] i = 1 To UBound(Ray, 1)
[COLOR="Navy"]For[/COLOR] j = i To UBound(Ray, 1)
jj = IIf(IsNumeric(Mid(Ray(j, 1), 2, 1)), 2, 3)
ii = IIf(IsNumeric(Mid(Ray(i, 1), 2, 1)), 2, 3)
[COLOR="Navy"]If[/COLOR] Val(Mid(Ray(j, 1), jj)) < Val(Mid(Ray(i, 1), ii)) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] Ac = 1 To Lst
Temp = Ray(i, Ac)
Ray(i, Ac) = Ray(j, Ac)
Ray(j, Ac) = Temp
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] j
[COLOR="Navy"]Next[/COLOR] i
Range("A1").Resize(UBound(Ray), Lst) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]