- 帖子
- 2035
- 主題
- 24
- 精華
- 0
- 積分
- 2031
- 點名
- 0
- 作業系統
- Win7
- 軟體版本
- Office2010
- 閱讀權限
- 100
- 性別
- 男
- 註冊時間
- 2012-3-22
- 最後登錄
- 2024-2-1
|
本帖最後由 c_c_lai 於 2013-8-30 15:25 編輯
回復 16# yliu
#17 樓是單選，你也可以改為多選：- Private Sub CommandButton1_Click()
- Dim g As Integer, E As Range, C As Range, 單號 As String, SS As String, Rng As Range
- Dim i As Integer
-
- With Sheets("login")
- 單號 = .ListBox2.Value
- Set Rng = .[B14:B24]
- SS = Application.Phonetic(Rng) ' 結合所有序號
- End With
-
- With Sheets("final").[A:A]
- If Application.CountIf(.Cells, 單號) > 1 Then
- .Replace 單號, "=xxx", xlWhole ' Replace 方法
- With .SpecialCells(xlCellTypeFormulas, xlErrors)
- .Cells = 單號
- For Each C In .Cells ' 比對到 序號 踢除 此序號
- If InStr(SS, C.Offset(, 1)) Then SS = Replace(SS, C.Offset(, 1), "") ' Replace 函數
- If SS = "" Then Exit Sub
- Next
- End With
- End If
-
- For Each E In Rng
- If E = "" Then Exit For
-
- If InStr(SS, E) Then ' 比對到 序號
- g = Application.CountA(.Cells) + 1 ' 讀取A欗有資料數的儲存格數 +1
- i = Application.CountA(Rng)
-
- .Cells(g, "A").Resize(1) = 單號
- .Cells(g, "B").Resize(1, 2) = E.Cells(1).Resize(1, 2).Value
- .Cells(g, "D").Resize(1, 6) = E.Cells(1, 4).Resize(1, 6).Value
- End If
- Next
- End With
-
- With Sheets("login")
- .ListBox1.Clear
- .[A14:E24] = ""
- .ListBox2 = ""
- End With
- End Sub
- Private Sub ListBox2_Change()
- Dim i As Integer, R As Integer
-
- ' ListBox1.Clear
- Sheets("login").[A14:E24] = ""
-
|
|
發表於 2013-8-30 08:50
| 
