Search file and data between several workbooks using Excel Macros

我有幾個file.file名不同.怎樣寫個macro可對應相關檔案同關鍵字找查資料.

例如, 我想用第一個file叫trade,內面有以下資料:
sku_alias Qty Supplier NamePO Date
256-10009-014,200 HANG 31015775
ACC-001-0415300HANG 31015888
ACC-001-0411500WAI 31015888

另外有兩個files, 分別是supplier hang，有以下資料:

sku_alias Qty Supplier NamePO Date
256-10009-014,200 HANG 31015775 8/7/10
ACC-001-0415300HANG 31015888 9/7/10

及 supplier wai, 其內存資料如下:
sku_alias Qty Supplier NamePO Date
ACC-001-0411500WAI 31015888 10/7/10

在trade的file中, 如何寫可以對照其supplier name查找相關的supplier file, 取用file中的日期?

 

答案：

簡單寫了段MACRO, 祇應付問題要求
Sub marco1()
Set bk1 = Workbooks("hang").Sheets("Sheet1").Range("A2:A100")
Set bk2 = Workbooks("wai").Sheets("Sheet1").Range("A2:A100")
For Each cell In Range("a2:a100")
If IsEmpty(cell) = True Then Exit Sub
Select Case cell.Offset(0, 2)
Case "HANG"
Set bk = bk1
Case "WAI"
Set bk = bk2
End Select
For Each c1 In bk
If c1 = cell And c1.Offset(0, 1) = cell.Offset(0, 1) And_
c1.Offset(0, 3) = cell.Offset(0, 3) Then
cell.Offset(0, 4) = c1.Offset(0, 4)
Exit For
End If
Next
Next
End Sub

如何一次過找尋/查詢數份WORKSHEET內容?

在一個excel檔中有幾份worksheet, 怎樣可以一次過keyword search 幾頁worksheet 呢？

Step1: 按住Keyboard的(Ctrl)不放然後點選所有要搜索的Worksheet

Step2: 同時按Keyboard的(Ctrl+F)

Step3: 於跳出的“尋找”視窗輸入Keyword就可以了