Sheet7
■ B C 2 CB1 1 3 CB2 2 4 CB10 10 5 CB11 11
<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas
Cell Formula C2 {=1*MID(B2,MATCH(TRUE,ISNUMBER(1*MID(B2,ROW($1:$9),1)),0),COUNT(1*MID(B2,ROW($1:$9),1)))} C3 {=1*MID(B3,MATCH(TRUE,ISNUMBER(1*MID(B3,ROW($1:$9),1)),0),COUNT(1*MID(B3,ROW($1:$9),1)))} C4 {=1*MID(B4,MATCH(TRUE,ISNUMBER(1*MID(B4,ROW($1:$9),1)),0),COUNT(1*MID(B4,ROW($1:$9),1)))} C5 {=1*MID(B5,MATCH(TRUE,ISNUMBER(1*MID(B5,ROW($1:$9),1)),0),COUNT(1*MID(B5,ROW($1:$9),1)))}
<tbody>
</tbody>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
Ok I will shortly, so the only way would be by using a formula?
[COLOR="Navy"]Sub[/COLOR] MG05Apr33
[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] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] i = 1 To UBound(Ray)
[COLOR="Navy"]For[/COLOR] j = i To UBound(Ray)
[COLOR="Navy"]If[/COLOR] Val(Mid(Ray(j, 1), 3)) < Val(Mid(Ray(i, 1), 3)) [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 | C10 | C2 | ||
4 | C100 | C100 | C3 | ||
5 | C101 | C2 | C4 | ||
6 | C102 | C20 | C5 | ||
7 | C103 | C3 | C6 | ||
8 | C104 | C30 | C7 | ||
9 | C105 | C4 | C8 | ||
10 | C106 | C40 | C9 | ||
11 | C107 | C5 | C10 | ||
12 | C108 | C50 | C11 | ||
13 | C109 | C6 | C12 | ||
14 | C11 | C60 | C13 | ||
15 | C110 | C7 | C14 | ||
16 | C111 | C70 | C15 | ||
17 | C112 | C8 | C16 | ||
18 | C113 | C80 | C17 | ||
19 | C114 | C9 | C18 | ||
20 | C115 | C90 | C19 | ||
21 | C116 | C61 | C20 | ||
22 | C117 | C41 | C21 | ||
23 | C118 | C11 | C22 | ||
24 | C119 | C71 | C23 | ||
25 | C12 | C31 | C24 | ||
26 | C120 | C51 | C25 | ||
27 | C121 | C81 | C26 | ||
28 | C122 | C21 | C27 | ||
29 | C13 | C101 | C28 | ||
30 | C14 | C91 | C29 | ||
31 | C15 | C52 | C30 | ||
32 | C16 | C72 | C31 | ||
33 | C17 | C22 | C32 | ||
34 | C18 | C42 | C33 | ||
35 | C19 | C32 | C34 | ||
36 | C2 | C82 | C35 | ||
37 | C20 | C62 | C36 | ||
38 | C21 | C12 | C37 | ||
39 | C22 | C102 | C38 | ||
40 | C23 | C92 | C39 | ||
41 | C24 | C23 | C40 | ||
42 | C25 | C103 | C41 | ||
43 | C26 | C63 | C42 | ||
44 | C27 | C33 | C43 | ||
45 | C28 | C83 | C44 | ||
46 | C29 | C53 | C45 | ||
47 | C3 | C43 | C46 | ||
48 | C30 | C13 | C47 | ||
49 | C31 | C73 | C48 | ||
50 | C32 | C93 | C49 | ||
51 | C33 | C54 | C50 | ||
52 | C34 | C14 | C51 | ||
53 | C35 | C104 | C52 | ||
54 | C36 | C84 | C53 | ||
55 | C37 | C34 | C54 | ||
56 | C38 | C24 | C55 | ||
57 | C39 | C74 | C56 | ||
58 | C4 | C44 | C57 | ||
59 | C40 | C64 | C58 | ||
60 | C41 | C94 | C59 | ||
61 | C42 | C45 | C60 | ||
62 | C43 | C25 | C61 | ||
63 | C44 | C85 | C62 | ||
64 | C45 | C55 | C63 | ||
65 | C46 | C75 | C64 | ||
66 | C47 | C65 | C65 | ||
67 | C48 | C35 | C66 | ||
68 | C49 | C105 | C67 | ||
69 | C5 | C15 | C68 | ||
70 | C50 | C95 | C69 | ||
71 | C51 | C66 | C70 | ||
72 | C52 | C86 | C71 | ||
73 | C53 | C76 | C72 | ||
74 | C54 | C16 | C73 | ||
75 | C55 | C46 | C74 | ||
76 | C56 | C36 | C75 | ||
77 | C57 | C106 | C76 | ||
78 | C58 | C56 | C77 | ||
79 | C59 | C26 | C78 | ||
80 | C6 | C96 | C79 | ||
81 | C60 | C87 | C80 | ||
82 | C61 | C107 | C81 | ||
83 | C62 | C17 | C82 | ||
84 | C63 | C57 | C83 | ||
85 | C64 | C37 | C84 | ||
86 | C65 | C47 | C85 | ||
87 | C66 | C67 | C86 | ||
88 | C67 | C27 | C87 | ||
89 | C68 | C77 | C88 | ||
90 | C69 | C97 | C89 | ||
91 | C7 | C48 | C90 | ||
92 | C70 | C108 | C91 | ||
93 | C71 | C28 | C92 | ||
94 | C72 | C68 | C93 | ||
95 | C73 | C38 | C94 | ||
96 | C74 | C18 | C95 | ||
97 | C75 | C78 | C96 | ||
98 | C76 | C58 | C97 | ||
Sheet3 |
If Val(Mid(Ray(j, 1), 2)) < Val(Mid(Ray(i, 1), 2)) Then