|
'不是已经给你写过了的吗?
Option Explicit
Sub test()
Dim arr, dic, i, j, pos, t
Set dic = CreateObject("scripting.dictionary")
With Sheets("小配方表")
arr = .Range("ad8:as" & .Cells(Rows.Count, "ad").End(xlUp).Row)
End With
pos = 1
For i = 2 To UBound(arr, 1)
If Len(arr(i, 1)) = 0 Then
For j = i + 1 To UBound(arr, 1)
If InStr(arr(j, 1), "产品") Then
i = j + 1: pos = j: Exit For
End If
Next
End If
For j = 2 To UBound(arr, 2)
t = LCase(arr(i, 1) & arr(pos, j))
dic(t) = arr(i, j)
Next j, i
With Sheets("配方汇总")
arr = .Range("a1:ae" & .Cells(Rows.Count, "a").End(xlUp).Row)
For i = 2 To UBound(arr, 1)
For j = 2 To UBound(arr, 2)
t = LCase(arr(i, 1) & arr(1, j))
arr(i, j) = IIf(dic.exists(t), dic(t), vbNullString)
Next j, i
.[a1].Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End With
End Sub |
|