- 帖子
- 2035
- 主題
- 24
- 精華
- 0
- 積分
- 2031
- 點名
- 0
- 作業系統
- Win7
- 軟體版本
- Office2010
- 閱讀權限
- 100
- 性別
- 男
- 註冊時間
- 2012-3-22
- 最後登錄
- 2024-2-1
|
回復 1# v03586 - ThisWorkbook:
- Option Explicit
- Private Sub Workbook_Open()
- Dim rng As Range, cts As Integer
- Dim sh As Worksheet, dic As Object
- Dim m As Variant
-
- Set dic = CreateObject("scripting.dictionary")
- Set sh = Sheets("資料庫")
- With Sheets("範例測試")
- With .ComboBox1
- .Clear
- ' .Value = ""
- .ColumnCount = 1
- .ColumnWidths = "80"
- .ColumnHeads = False
-
- For Each rng In Range(sh.[A2], sh.[A2].End(xlDown))
- dic(rng.Value) = rng.Value
- Next
-
- m = dic.Items ' 字典存入到 m 變數中
- For cts = 0 To UBound(dic.Keys)
- .AddItem m(cts)
- Next
- .Value = "" ' m(0) = "A"
- End With
- End With
- End Sub
- 工作表3 (範例測試)：
- Private Sub ComboBox1_Change()
- Dim sh As Range, c As Variant
-
- Sheets("範例測試").[A3:I65535].Clear
- Set sh = Sheets("範例測試").[A3]
- ' 這個範例會在第一張工作表上的 A1:A500 範圍內尋找值為 2 的所有儲存格，
- ' 並將這些儲存格的值變更為 5。
- With Sheets("資料庫").Range("A2:A" & [A2].End(xlDown).Row)
- Set c = .Find(ComboBox1.Value, LookIn:=xlValues)
- If Not c Is Nothing Then
- firstAddress = c.Address
- Do
- c.Resize(, 5).Copy sh
- sh.Offset(0, 8) = c.Offset(0, 6)
-
- Set sh = sh.Offset(1)
- Set c = .FindNext(c)
- Loop While Not c Is Nothing And c.Address <> firstAddress
- End If
- End With
- End Sub
|
|
發表於 2016-7-25 07:01
| 
