- 帖子
- 522
- 主題
- 36
- 精華
- 1
- 積分
- 603
- 點名
- 0
- 作業系統
- win xp sp3
- 軟體版本
- Office 2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-12-13
- 最後登錄
- 2021-7-11
|
2#
發表於 2014-4-22 16:16
| 只看該作者
回復 1# symis
我只能想到這個笨方法:
E4 陣列公式：
=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C4,"")))
向下拉
E8 陣列公式：
=SUM(E4:E5)
有錯誤時, 輸入有點麻煩：
(至少要操作兩次以上, 看準要修改的格子再修改)
總數有錯誤時, 不給警告, 以免太麻煩, 反正全部輸入完畢就ok了,
 - Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Dim a1, b1, c1, d1 As Integer, Rng As Range
- Set Rng = Worksheets(1).Range("J3:J10")
-
-
- If Not Intersect(Target, Rng) Is Nothing Then
- [E4].FormulaArray = _
- "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C4,"""")))"
- [E5].FormulaArray = _
- "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C5,"""")))"
- [E6].FormulaArray = _
- "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C6,"""")))"
- [E7].FormulaArray = _
- "=SUM(LEN(UPPER($J$3:$J$10))-LEN(SUBSTITUTE(UPPER($J$3:$J$10),C7,"""")))"
- [E8] = "=SUM(E4:E5)"
-
- If [E4] > 1 Then
- MsgBox "A 最多只能有1個", vbCritical
- End If
- If [E5] > 2 Then
- MsgBox "B 最多只能有2個", vbCritical
- End If
- If [E6] < 4 Then
- MsgBox "C 最少要有4個", vbCritical
- End If
- If [E7] < 1 Then
- MsgBox "D 最少要有1個", vbCritical
- End If
- End If
-
- End Sub
|
|
