|
请各位老师帮忙写个VLOOKUP数据匹配代码1.匹配姓名:数据源在表三(排班)
2.匹配生产型态,匹配抛光机台:数据源在表二(抛光数据)
以下是我使用的代码,因数据太多,结果造成Excel 卡死
Sub 匹配姓名()
On Error Resume Next
Application.EnableEvents = False
Dim ary As Long
ary = Range("A65536").End(xlUp).Row
For i = 3 To ary
Cells(i, 2) = "=VLOOKUP(RC[-1],排班!C[-1]:C,2,0)"
Next
Application.EnableEvents = True
End Sub
我把代码复制在这里:
Dim arr
Dim brr
Worksheets("排班").Activate
arr = Worksheets("排班").Range(Cells(2, 1), Cells(Worksheets("排班").[a65000].End(3).Row, 2))
Worksheets("抛光数据").Activate
brr = Worksheets("抛光数据").Range(Cells(2, 1), Cells(Worksheets("抛光数据").[a65000].End(3).Row, 3))
Worksheets("数据源").Activate
For i = 3 To [a65000].End(3).Row
For k = 1 To UBound(arr)
If Cells(i, 1) = arr(k, 1) Then
Cells(i, 2) = arr(k, 2)
End If
Next k
Next i
For i = 3 To [a65000].End(3).Row
For k = 1 To UBound(brr)
If Cells(i, 7) = UBound(brr) Then
Cells(i, 7) = brr(k, 2)
Cells(i, 8) = brr(k, 1)
End If
Next k
Next i
|
|