- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 142
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2026-7-16
                
|
7#
發表於 2014-7-31 10:36
| 只看該作者
回復 6# Duck - Private Sub ComboBox1_Change()
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- Set d3 = CreateObject("Scripting.Dictionary")
- d2("CHT_IDX") = "B欄不重複數" 'L:M的欄位名稱"
- d3("CHT_IDX") = "B欄不重複數"
- With 工作表2
- Set Rng = 工作表2.[A1]
- .[A:E].ClearContents '清除之前篩選結果
- 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)" 'J欄公式
- For Each a In .Range(.[B2], .[B1].End(xlDown)) 'B欄資料做迴圈
- d(a.Value) = "" '儲存DATESEQ不重複清單
- d1(a.Offset(, 3).Value) = "" '儲存PRICE_NAME不重複清單
- d3(a.Offset(, -1).Value) = _
- IIf(InStr(d3(a.Offset(, -1).Value), a) = 0, d3(a.Offset(, -1).Value) & ";" & a, d3(a.Offset(, -1).Value)) '以A欄為索引，若未含B欄字串，則以分號;連結B欄字串
- d2(a.Offset(, -1).Value) = UBound(Split(d3(a.Offset(, -1).Value), ";")) '以分號切割字串，計算出陣列元素數量，即為同CHT_IDX的不重複B欄數量
- Next
- .Range("G1").CurrentRegion.Offset(1).ClearContents
- .[L:M].ClearContents '清除L:M欄
- '寫入G:M欄
- .[G2].Resize(d.Count, 1) = Application.Transpose(d.Keys)
- .[I2].Resize(d1.Count, 1) = Application.Transpose(d1.Keys)
- .[L1].Resize(d3.Count, 1) = Application.Transpose(d3.Keys)
- .[M1].Resize(d2.Count, 1) = Application.Transpose(d2.items)
- .[J2].Resize(d1.Count, 1).FormulaR1C1 = mystr
- .[H2] = d.Count
- End With
- Unload Me '卸載表單
- End Sub
|
|
發表於 2014-7-24 18:32
| 
發表於 2019-4-15 09:48
| 
