|
按新要求小改了一下,应该没问题了。
- Sub grf()
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- brr = Range("q2:q" & [q65536].End(3).Row) '标准
- With CreateObject("vbscript.regexp")
- .Global = True
- .Pattern = "\d+"
- For i = 1 To UBound(brr) '把标准里的所有数字提取出来,组成",500,95,95"类型的字符串,以便比较
- x = brr(i, 1)
- Set ma = .Execute(x)
- For Each m In ma
- d(x) = d(x) & "," & m '数字连成字符串
- d1(x) = d1(x) + 1 '由几个数值组成
- Next
- Next
- arr = Range("a2:a" & [a65536].End(3).Row)
- For i = 1 To UBound(arr)
- y = arr(i, 1)
- If Not d.exists(y) Then
- xstr = ""
- Set ma = .Execute(y)
- For Each m In ma
- xstr = xstr & "," & m '数字连成字符串
- Next
- yrr = Split(xstr, ",") '待比较的数
- n = UBound(yrr)
-
- Delta = 1000
- For Each x In d.keys
- If n = d1(x) Then '有相同个数值的作比较
- xrr = Split(d(x), ",") '标准
- s = 0
- For k = 1 To n
- If Val(xrr(k)) - Val(yrr(k)) < 0 Then Exit For '标准规格各项必须大于待比较的产品规格
- s = s + Abs(Val(xrr(k)) - Val(yrr(k))) '比较核心:标准各数和待比较数对应位置相减的绝对值相加最小
- Next
- If s < Delta And k = n + 1 Then Delta = s: arr(i, 1) = x
- End If
- Next
- End If
- Next
- End With
- [b2].Resize(UBound(arr)) = arr
- End Sub
复制代码 |
|