暱稱: 温奇 頭銜: 議論者
中學生
- 帖子
- 104
- 主題
- 5
- 精華
- 0
- 積分
- 110
- 點名
- 0
- 作業系統
- win7
- 軟體版本
- Office2007
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2017-11-14
- 最後登錄
- 2019-4-30
 
|
回復 11# Qin
搜尋資料.xlsm- Sub 搜尋表單()
- Dim F1, F2 As Variant
- F1 = Sheets("搜尋").Range("B1")
- Sheets("搜尋").Select
- Sheets("搜尋").Range([A3], [J3].End(xlDown)) = ""
- If F1 = "" Then
- MsgBox "您未輸入條件"
- Exit Sub
- End If
- For I = 2 To Sheets("資料庫").UsedRange.Rows.Count
- If Sheets("資料庫").Cells(I, 4) = F1 Or Sheets("資料庫").Cells(I, 6) = F1 Or Sheets("資料庫").Cells(I, 7) = F1 Then
- Worksheets("資料庫").Range("A" & I, "J" & I).Copy Destination:=Worksheets("搜尋").Range("A" & Application.CountA(Sheets("搜尋").Columns("A:A")) + 1)
- End If
- Next I
- MsgBox "您輸入" & "條件" & "[ " & F1 & " ]" & "共計" & Application.CountA(Sheets("搜尋").Columns("A:A")) - 2 & "筆資料"
- End Sub
- Sub Serach()
- Application.DisplayAlerts = False
- Application.ScreenUpdating = False
- Dim a, b, c As String
- Dim f1, f2, f3 As Workbook
- Set f1 = Sheets("資料庫")
- Set f2 = Sheets("搜尋資料")
- a = Application.InputBox("請輸入查詢的項目代碼:1.公司 2.編號 3.品名", "輸入搜尋項目")
- b = Application.InputBox("請輸入查詢的內容名稱", "輸入搜尋內容")
- If a = "" Or a = False Or b = "" Or b = False Then
- Exit Sub
- Else
- Select Case a
- Case "1"
- X = 3
- Case "2"
- X = 5
- Case "3"
- X = 6
- Case Else
- Exit Sub
- End Select
- f1.Select
- For Each aa In Range([A2], [A2].End(xlDown))
- If aa.Offset(, X) = b Then
- f2.Select
- n = n + 1
- NextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
- Cells(NextRow, 1) = n
- Cells(NextRow, 2).Resize(1, 9) = aa.Offset(0, 1).Resize(1, 9).Value
- Cells(NextRow, 15).Resize(1, 1) = aa.Offset(0, 0).Resize(1, 1).Value
- End If
- Next
- End If
- f2.Cells(1, 15) = "total: " & Application.CountA(f2.Range("A:A")) - 1
- f2.Select
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
- Sub clase()
- [A3:O60000] = ""
- [O1] = "total: 0"
- End Sub
|
|
發表於 2018-7-12 05:42
| 
