- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 97
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2025-4-22
               
|
2#
發表於 2014-7-25 10:55
| 只看該作者
本帖最後由 Hsieh 於 2014-7-25 10:59 編輯
回復 1# Duck
不重複定義是否B&D不重複?
如果是的話
插入自訂表單，布置一個下拉清單
表單模組中程式碼如下- Private Sub ComboBox1_Change()
- Set d = CreateObject("Scripting.Dictionary")
- With 工作表2
- Set Rng = 工作表2.[A1]
- .[A:E].Clear
- With 工作表1
- With .Range("A1").CurrentRegion
- .AutoFilter 4, ComboBox1
- .SpecialCells(xlCellTypeVisible).Copy Rng
- .AutoFilter
- End With
- End With
- mystr = "=COUNTIF(C5,RC9)/(COUNTA(C7)-1)"
- For Each a In .Range(.[B2], .[B1].End(xlDown))
- d(a & a.Offset(, 3)) = Array(a, "", a.Offset(, 3))
- Next
- .Range("G1").CurrentRegion.Offset(1).ClearContents
- .[G2].Resize(d.Count, 3) = Application.Transpose(Application.Transpose(d.items))
- .[J2].Resize(d.Count, 1).FormulaR1C1 = mystr
- .[H2] = d.Count
- End With
- Unload Me
- End Sub
- Private Sub UserForm_Initialize()
- Set d = CreateObject("Scripting.Dictionary")
- With 工作表1
- For Each a In .Range(.[D2], .[D2].End(xlDown))
- d(a.Text) = ""
- Next
- End With
- ComboBox1.List = d.keys
- End Sub
 上傳用.zip (28.34 KB)
|
|
發表於 2014-7-28 19:29
| 
