|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
'流水代码,数据量大估计效率较差
'写完再也不想看了,自己试一下
Option Explicit
Sub test()
Dim arr, brr, i, j, k, t1, t2, pos, crr
With Sheets("工序价格表")
arr = .Range("a1:h" & .Cells(Rows.Count, "b").End(xlUp).Row)
End With
With Sheets("记工表")
brr = .Range("b2:d" & .Cells(Rows.Count, "b").End(xlUp).Row)
ReDim crr(1 To UBound(brr, 1), 1 To 1)
For i = 1 To UBound(brr, 1)
If Len(brr(i, 1)) > 0 And Len(brr(i, 3)) > 0 Then
For j = 1 To UBound(arr, 1)
If brr(i, 3) = arr(j, 1) Then Exit For
Next
If j < UBound(arr, 1) + 1 Then
pos = j
If InStr(brr(i, 1), "*") Then
t2 = Split(brr(i, 1), "*")
For j = pos + 1 To UBound(arr, 1)
If Len(arr(j, 1)) = 0 Then Exit For
t1 = Split(arr(j, 1), "-")
If Val(t2(0)) >= Val(t1(0)) And Val(t2(0)) <= Val(t1(1)) Then
For k = 2 To UBound(arr, 2)
If Len(arr(pos, k)) = 0 Then Exit For
t1 = Split(arr(pos, k), "-")
If Val(t2(1)) >= Val(t1(0)) And Val(t2(1)) <= Val(t1(1)) Then
crr(i, 1) = arr(j, k): Exit For
End If
Next
End If
Next
Else
If Len(brr(i, 2)) > 0 Then
For j = pos + 1 To UBound(arr, 1)
If Len(arr(j, 1)) = 0 Then Exit For
t1 = Split(arr(j, 1), "-")
If Val(brr(i, 1)) >= Val(t1(0)) And Val(brr(i, 1)) <= Val(t1(1)) Then
For k = 2 To UBound(arr, 2)
If Len(arr(pos, k)) = 0 Then Exit For
If brr(i, 2) = arr(pos, k) Then
crr(i, 1) = arr(j, k): Exit For
End If
Next
End If
Next
End If
End If
End If
End If
Next
.[f2].Resize(UBound(crr, 1), 1) = crr
End With
End Sub |
|