- 帖子
- 522
- 主題
- 36
- 精華
- 1
- 積分
- 603
- 點名
- 0
- 作業系統
- win xp sp3
- 軟體版本
- Office 2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-12-13
- 最後登錄
- 2021-7-11
|
感謝 GBKEE 版大的指正, 方能完成- '每個[序號]在整個WorkBook的第3頁以後, 是否均只出現一次?不會重覆?
- '這個VBA Code是以不重覆為準而寫
- Sub 快速序號查詢()
- Dim D As Object, Sh As Worksheet, fRng As Range, sRng As Range
- Dim I As Integer, J As Integer, sNum As String, FstAddr As String
- Set Sh = Sheets("出貨輸入")
- For I = 5 To Sh.[B5].End(xlDown).Row
- sNum = Sh.Cells(I, 2)
- For J = 3 To Sheets.Count
- Sheets(J).Activate
- Set sRng = Sheets(J).Range("B3", "B" & [B2].End(xlDown).Row) '設定被搜尋的Range
- sRng.Select
- ' On Error Resume Next
- Set fRng = sRng.Find(sNum, lookat:=xlWhole) '在Sheets(J)的sRng中尋找 序號
- If Not fRng Is Nothing Then '有找到
- Sh.Cells(I, 1) = fRng.Offset(0, -1) '拷貝相關資料
- Sh.Cells(I, 3) = fRng.Offset(0, 1)
- fRng.Offset(0, 3).Select
- fRng.Offset(0, 3).Resize(1, 4).Copy Sh.Cells(I, 4)
- '你給的圖與測試檔有出入, 圖有[出貨/借出日期],而測試檔的[出貨/借出日期]是空的?
- GoTo Next1 '拷貝完換下一個序號
- End If
- Next
- Next1:
- Next
- Sh.Activate
- End Sub
|
|
發表於 2015-11-24 09:59
| 
