- 帖子
- 835
- 主題
- 6
- 精華
- 0
- 積分
- 915
- 點名
- 16
- 作業系統
- Win 10,7
- 軟體版本
- 2019,2013,2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2010-5-3
- 最後登錄
- 2024-11-14
|
2#
發表於 2011-11-3 21:37
| 只看該作者
本帖最後由 luhpro 於 2011-11-3 21:55 編輯
1. 工作表(IQC查詢)這邊，如果再輸入新的要查詢資料的值，原本的欄位資料會被蓋掉，請問有什麼方法解決?
icefire722 發表於 2011-11-2 19:42 
要讓它不覆蓋前查資料的方法我想到的有兩種:
1. 每次都找最後一行資料, 然後新增到其底下一行.(查詢資料列號會一直累加下去)- Sub DateButton_Click()
- Dim srcRange As Range, fndRange As Range
- Dim fstAddress As String, CurRow As Long
- Sheets("IQC查詢").Select
- Set srcRange = Sheets("每日IQC").Range("A19").CurrentRegion.Columns(3)
- Set fndRange = srcRange.Find(what:=Range("B3").Value)
- If Not fndRange Is Nothing Then
- fstAddress = fndRange.Address
- CurRow = Cells(7, 1).End(xlDown).Row + 1
- Do
- Cells(CurRow, 1).Value = fndRange.Offset(, -2).Value
- Cells(CurRow, 2).Value = fndRange.Offset(, 0).Value
- Cells(CurRow, 3).Value = fndRange.Offset(, 2).Value
- Set fndRange = srcRange.FindNext(after:=fndRange)
- Loop Until fndRange.Address = fstAddress
- Else
- MsgBox "無此批號!!"
- End If
- End Sub
- Public CurRow As Long
- Option Explicit
- Sub DateButton_Click()
- Dim srcRange As Range, fndRange As Range
- Dim fstAddress As String
- CurRow = CurRow + 1 - (CurRow < 6) * 6
- Sheets("IQC查詢").Select
- Set srcRange = Sheets("每日IQC").Range("A19").CurrentRegion.Columns(3)
- Set fndRange = srcRange.Find(what:=Range("B3").Value)
- If Not fndRange Is Nothing Then
- fstAddress = fndRange.Address
- Do
- Cells(CurRow, 1).Value = fndRange.Offset(, -2).Value
- Cells(CurRow, 2).Value = fndRange.Offset(, 0).Value
- Cells(CurRow, 3).Value = fndRange.Offset(, 2).Value
- Set fndRange = srcRange.FindNext(after:=fndRange)
- Loop Until fndRange.Address = fstAddress
- Else
- MsgBox "無此批號!!"
- End If
- End Sub
題外話 :
若需要用變數來存放列號以便使用時不建議用 Integer ,
因為它只能存到 32767 ,
距離舊版 Excel 的最大列號 65536 是不夠用的. |
|
