[算表] 多資料欄位跨檔案條件判斷
軟體: EXCEL
版本: 2010
檔案1 test.xlsm
檔案2 date.xls
以檔案1的欄位以搜尋的的方法來找尋是否有出現在檔案2
附上程式碼
Sub FindAddress()
Dim s As Integer
Dim r As Integer
Dim GCell, txt As Range
Dim MySheet As String
'路徑
MyPath = "d:\test\"
'檔案名稱
MyWB = "date.xls"
MySheet = ActiveSheet.Name
'關顯示開唯讀
Application.ScreenUpdating = False
Workbooks.Open Filename:=MyPath & MyWB, ReadOnly:=True
e = 5
For r = 17 To 36
i = r
Set txt = Cells(r, e)
Set GCell = ActiveSheet.Cells.Find(What:=txt)
If Not GCell Is Nothing Then
With ThisWorkbook.ActiveSheet.Range("f" & (i))
.Value = "ok"
End With
Else
With ThisWorkbook.ActiveSheet.Range("f" & (i))
.Value = "no"
End With
End If
Next r
'開顯示不存檔
ActiveWorkbook.Close savechanges:=False
Application.ScreenUpdating = True
End Sub
問題1:無法指定檔案1的欄位來做搜尋,他都會跳到檔案2欄位做為搜尋的條件
請問是我哪裡寫錯了嗎??
問題2:因為檔案2會以月份做為工作表,所以之後會輸入的工作表至少會有12頁,
這樣開檔搜尋之後又關檔效率會好嗎?
請各位多多指教~~~謝謝!!
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 125.230.134.117
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1473445019.A.34F.html
→
09/10 07:15, , 1F
09/10 07:15, 1F
→
09/10 07:15, , 2F
09/10 07:15, 2F
→
09/10 16:33, , 3F
09/10 16:33, 3F
→
09/10 16:35, , 4F
09/10 16:35, 4F
→
09/10 16:36, , 5F
09/10 16:36, 5F
→
09/10 16:44, , 6F
09/10 16:44, 6F
→
09/10 16:58, , 7F
09/10 16:58, 7F
→
09/10 16:59, , 8F
09/10 16:59, 8F
→
09/10 17:07, , 9F
09/10 17:07, 9F
→
09/10 17:28, , 10F
09/10 17:28, 10F
→
09/10 17:31, , 11F
09/10 17:31, 11F
→
09/10 17:38, , 12F
09/10 17:38, 12F
→
09/10 19:11, , 13F
09/10 19:11, 13F
推
09/10 23:54, , 14F
09/10 23:54, 14F
→
09/10 23:55, , 15F
09/10 23:55, 15F
→
09/10 23:55, , 16F
09/10 23:55, 16F
→
09/10 23:55, , 17F
09/10 23:55, 17F
→
09/10 23:57, , 18F
09/10 23:57, 18F
推
09/10 23:58, , 19F
09/10 23:58, 19F
→
09/10 23:58, , 20F
09/10 23:58, 20F
→
09/10 23:59, , 21F
09/10 23:59, 21F
推
09/11 00:02, , 22F
09/11 00:02, 22F
→
09/11 00:02, , 23F
09/11 00:02, 23F
推
09/11 00:04, , 24F
09/11 00:04, 24F
推
09/11 00:06, , 25F
09/11 00:06, 25F
→
09/11 00:07, , 26F
09/11 00:07, 26F
推
09/11 00:12, , 27F
09/11 00:12, 27F
→
09/11 00:12, , 28F
09/11 00:12, 28F
→
09/11 00:13, , 29F
09/11 00:13, 29F
→
09/11 00:13, , 30F
09/11 00:13, 30F
→
09/11 00:13, , 31F
09/11 00:13, 31F
→
09/11 00:13, , 32F
09/11 00:13, 32F
→
09/11 03:40, , 33F
09/11 03:40, 33F
→
09/11 03:44, , 34F
09/11 03:44, 34F
→
09/11 03:46, , 35F
09/11 03:46, 35F
推
09/11 04:07, , 36F
09/11 04:07, 36F
→
09/11 04:07, , 37F
09/11 04:07, 37F
http://goo.gl/20mCMX -----test.xlsm
http://goo.gl/pbjvU5 -----dete.xls
附上相關檔案~~
※ 編輯: ahow0917 (114.41.83.250), 09/11/2016 16:32:23
推
09/11 16:34, , 38F
09/11 16:34, 38F
→
09/11 16:47, , 39F
09/11 16:47, 39F
→
09/11 18:22, , 40F
09/11 18:22, 40F
→
09/11 23:41, , 41F
09/11 23:41, 41F