- ©«¤l
- 522
- ¥DÃD
- 36
- ºëµØ
- 1
- ¿n¤À
- 603
- ÂI¦W
- 0
- §@·~¨t²Î
- win xp sp3
- ³nÅ骩¥»
- Office 2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-12-13
- ³Ì«áµn¿ý
- 2021-7-11
|
¥»©«³Ì«á¥Ñ yen956 ©ó 2014-4-1 19:48 ½s¿è
¸Õ¸Õ¬Ý:- Option Base 1
- Option Explicit
- Private Sub CommandButton1_Click()
- Dim sh1, sh3 As Worksheet
- Dim Rng, cel As Range
- Dim strR, strL As String
- Dim i, j, r, blankCol, cnt(1 To 10), cnt2(1 To 10) As Integer
- Dim num1, num2
- num1 = Array("1", "2", "3", "4", "5", "6", "7", "8", "9")
- num2 = Array("¤@", "¤G", "¤T", "¥|", "¤", "¤»", "¤C", "¤K", "¤E")
-
- Set sh1 = Sheets("¿é¤J¸ê®Æ")
- Set sh3 = Sheets("¤ÀÃþµ²ªG")
-
- '²M°£ sh3 ªº¸ê®Æ
- sh3.[C3].Resize(41, 200) = ""
-
- '¥H§Aªº¥Ü½dÀɨӻ¡, ¿é¤J°Ï±q [A4] ¶}©lºâ, ¦@ 14¦C, 9 Äæ
- '½Ð¦Û¦æ¨Ì ¹ê»Ú¿é¤J½d³ò קï Resize(14, 9) ªº¼Æ¾Ú
- Set Rng = sh1.[A4].Resize(14, 9)
-
- For i = 1 To 10
- cnt(i) = 0
- cnt2(i) = 0
- Next
-
- For Each cel In Rng
-
- 'Y cel ¬OªÅÈ, ´«¤U¤@Ó
- If cel.Value = "" Then GoTo next1:
-
- '¨ú±o³Ì¥k¤@½X
- strR = Right(cel, 1)
-
- '¥h°£³Ì¥k¤@½X
- strL = Left(cel, Len(cel) - 1)
-
- '¤ñ¹ï strR ¦b num1 ¤¤ ±Æ²Ä´XÓ
- j = Application.Match(strR, num1, 0)
-
- 'Y match ²£¥Í¿ù»~ ¡÷ ªí¥Ü strR ¤£¦b°}¦C num1 ¤¤,
- If Application.IsError(j) Then
-
- '«hÄ~Äò¤ñ¹ï strR ¦b num2 ¤¤ ±Æ²Ä´XÓ
- j = Application.Match(strR, num2, 0)
-
- 'Y match ²£¥Í¿ù»~ ¡÷ ªí¥Ü strR ¤]¤£¦b°}¦C num2 ¤¤,
- If Application.IsError(j) Then
-
- 'strR §¡¤£¦b num1¡Bnum2 ¤¤,
- '¡÷ ªí¥Ü strR À³ÂkÃþ¦b unclassified ¤¤,
- '²Îp unclassified ªºÁ`¼Æ
- cnt(10) = cnt(10) + 1
-
- '¨ú±o ªÅ¥ÕÀx¦s®æ ªº ÄæÈ
- blankCol = sh3.Cells(cnt2(10) + 40, 256).End(xlToLeft).Column + 1
- If blankCol < 3 Then blankCol = 3
-
- sh3.Cells(cnt2(10) + 40, blankCol) = cel
-
- '«·spºâ¦s¤J unclassified Ãþªº¦CÈ
- cnt2(10) = cnt2(10) + 1
- If cnt2(10) >= 3 Then cnt2(10) = 0
-
- Else
- '²Îp¦U²ÕªºÁ`¼Æ
- cnt(j) = cnt(j) + 1
-
-
- '¨ú±o ªÅ¥ÕÀx¦s®æ ªº ÄæÈ
- blankCol = sh3.Cells(j * 4 + cnt2(j), 256).End(xlToLeft).Column + 1
- If blankCol < 3 Then blankCol = 3
-
- sh3.Cells(j * 4 + cnt2(j), blankCol) = strL
-
- '«·spºâ¦s¤J ¦U²Õ ªº¦CÈ
- cnt2(j) = cnt2(j) + 1
- If cnt2(j) >= 3 Then cnt2(j) = 0
-
- End If
- Else
- '²Îp¦U²ÕªºÁ`¼Æ
- cnt(j) = cnt(j) + 1
-
- '¨ú±o ªÅ¥ÕÀx¦s®æ ªº ÄæÈ
- blankCol = sh3.Cells(j * 4 + cnt2(j), 256).End(xlToLeft).Column + 1
- If blankCol < 3 Then blankCol = 3
-
- sh3.Cells(j * 4 + cnt2(j), blankCol) = strL
-
- '«·spºâ¦s¤J ¦U²Õ ªº¦CÈ
- cnt2(j) = cnt2(j) + 1
- If cnt2(j) >= 3 Then cnt2(j) = 0
- End If
-
- next1:
- Next
-
- '¶ñ¤J¦U²ÕÁ`¼Æ
- For i = 1 To 10
- sh3.Cells(i * 4 - 1, 3) = cnt(i)
- Next
- End Sub
½Æ»s¥N½X ¦p¤U¹Ï:
|
|