|
- Sub grf()
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Set d3 = CreateObject("scripting.dictionary")
- Range("d2:d" & [a65536].End(3).Row) = "错误"
- arr = Range("a1").CurrentRegion
- brr = Range("h1").CurrentRegion
- xmax = Application.WorksheetFunction.Max(Range("J2:J" & [J65536].End(3).Row)) 'J列最大值
- For i = 2 To UBound(brr) '表二
- pm = brr(i, 1) '品名
- If Not d2.exists(pm) Then d2(pm) = brr(i, 4)
- d3(pm) = d3(pm) & "," & i '表二品名和行号相对应
- Next
- For i = 2 To UBound(arr) '表一
- pm = arr(i, 1)
- tmp = xmax + 1
- If Not d.exists(pm) Then '如果表一第一次出现,取表二第一次出现的日期
- arr(i, 4) = d2(pm)
- d(pm) = ""
- xrr = Split(d3(pm), ",")
- Else
- sl = arr(i - 1, 3) '表1上一行数量
- For k = 1 To UBound(xrr) '遍历表二该品名各行
- kk = Val(xrr(k))
- If brr(kk, 3) > sl And brr(kk, 3) < tmp Then '找到表二大于表1上一行数量的最小值
- tmp = brr(kk, 3)
- arr(i, 4) = brr(kk, 4)
- End If
- Next
- End If
- Next
- Range("d1").Resize(UBound(arr)) = Application.Index(arr, , 4)
- End Sub
复制代码 |
|