|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim wb As Workbook
- Dim ws As Worksheet
- Dim mypath$, myname$
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:b" & r)
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = i
- Next
- End With
- mypath = ThisWorkbook.Path & ""
- myname = "匹配源表:多个工作表.xlsx"
- If Dir(mypath & myname) = "" Then
- MsgBox mypath & myname & "不存在!"
- End If
- Set wb = GetObject(mypath & myname)
- With wb
- For Each ws In .Worksheets
- With ws
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- If r > 1 Then
- brr = .Range("a2:b" & r)
- For i = 1 To UBound(brr)
- If d.exists(brr(i, 1)) Then
- m = d(brr(i, 1))
- arr(m, 2) = brr(i, 2)
- End If
- Next
- End If
- End With
- Next
- .Close False
- End With
- With Worksheets("sheet1")
- .Range("a2").Resize(UBound(arr), UBound(arr, 2)) = arr
- End With
- End Sub
复制代码 |
|