- 帖子
- 522
- 主題
- 36
- 精華
- 1
- 積分
- 603
- 點名
- 0
- 作業系統
- win xp sp3
- 軟體版本
- Office 2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-12-13
- 最後登錄
- 2021-7-11
|
5#
發表於 2016-3-19 15:38
| 只看該作者
本帖最後由 yen956 於 2016-3-19 15:58 編輯
'1. 可能是臨時 Key in 的關係, 欄A不是Excel的日期格式,
Sub 轉換為Excel日期格式()
Dim I As Integer, Ar
For I = 2 To Range("A" & Rows.Count).End(xlUp).Row
Ar = Split(Cells(I, 1), ".")
x = Trim(Str(Val(Ar(0)) + 1911)) & "/" & Ar(1) & "/" & Ar(2)
Cells(I, 1) = Trim(Str(Val(Ar(0)) + 1911)) & "/" & Ar(1) & "/" & Ar(2)
Next
End Sub
'2. 若 類別 長度一樣- Sub CountDist()
- Dim Rng As Range, I As Integer, s1 As String, E
- Set d = CreateObject("Scripting.Dictionary")
- [F5:Q5] = "": [F9:Q9] = ""
- Set Rng = Range([A2], "A" & Range("A" & Rows.Count).End(xlUp).Row)
-
- For Each E In Rng
- d(Str(Month(E.Value)) & E(1, 3).Value & E(1, 2)) = Month(E.Value)
- Next
-
- For I = 0 To d.Count - 1
- If Right(d.keys()(I), 1) = "A" Then '若 類別 為 "A"(長度為1)
- Cells(5, d.items()(I) + 5) = Cells(5, d.items()(I) + 5) + 1
- ElseIf Right(d.keys()(I), 1) = "B" Then '若 類別 為 "B"(長度為1)
- Cells(9, d.items()(I) + 5) = Cells(9, d.items()(I) + 5) + 1
- End If
- Next
- End Sub
- '3. 若 類別 長度不一
- Sub CountDist2()
- Dim Rng As Range, I As Integer, s1 As String, E
- Set d = CreateObject("Scripting.Dictionary")
- [F5:Q5] = "": [F9:Q9] = ""
- Set Rng = Range([A2], "A" & Range("A" & Rows.Count).End(xlUp).Row)
-
- For Each E In Rng
- '輔助欄(欄S), 將 類別 左邊用 # 字元補足為10個字元
- E(1, 19) = "=right(""#########""&RC[-17],10)"
- d(Str(Month(E.Value)) & E(1, 3).Value & E(1, 19)) = Month(E.Value)
- Next
-
- For I = 0 To d.Count - 1
- If Right(d.keys()(I), 3) = "Axx" Then '若 類別 為 "Axx"(長度為3)
- Cells(5, d.items()(I) + 5) = Cells(5, d.items()(I) + 5) + 1
- ElseIf Right(d.keys()(I), 2) = "Bx" Then '若 類別 為 "Bx"(長度為2)
- Cells(9, d.items()(I) + 5) = Cells(9, d.items()(I) + 5) + 1
- End If
- Next
- End Sub
|
|
發表於 2016-3-19 12:09
| 
