|
发表于 2011-10-26 19:53
|
显示全部楼层
本楼为最佳答案
Sub test()
Dim ar1(), ar2(), ar3()
Set d = CreateObject("Scripting.Dictionary")
i% = [a65536].End(xlUp).Row
ar1 = Range("a2:g" & i).Value
For i = UBound(ar1) To 1 Step -1
d(ar1(i, 1) & ar1(i, 2)) = i
Next
i = [i65536].End(xlUp).Row
ar2 = Range("i2:m" & i).Value
ReDim ar3(1 To UBound(ar2), 1 To 1)
For i = 1 To UBound(ar2)
i2% = d(ar2(i, 1) & ar2(i, 2))
Do While i2 <= UBound(ar1)
If InStr(ar1(i2, 3), "以下") Then
ke% = Val(ar1(i2, 3))
ElseIf InStr(ar1(i2, 3), "-") Then
ke% = Val(Split(ar1(i2, 3), "-")(1))
Else
ke% = 1000
End If
If ar2(i, 3) <= ke Then
If ar1(i2, 4) = "" Then
ar3(i, 1) = ar1(i2, 6)
i2 = UBound(ar1)
Else
Do While i2 <= UBound(ar1)
If InStr(ar1(i2, 4), "以下") Then
ke% = Val(ar1(i2, 4))
ElseIf InStr(ar1(i2, 4), "-") Then
ke% = Val(Split(ar1(i2, 4), "-")(1))
Else
ke% = 1000
End If
If ar2(i, 5) <= ke Then
If ar2(i, 4) < 140 Then
ar3(i, 1) = ar1(i2, 6)
Else
ar3(i, 1) = ar1(i2, 7)
End If
i2 = UBound(ar1)
End If
i2 = i2 + 1
Loop
End If
End If
i2 = i2 + 1
Loop
Next
[n2].Resize(i - 1) = ar3
End Sub
|
|