|
特殊判断不是很多,用循环应该已经很快了。我觉得这个数量的处理,没有必要特别追求速度;简单可读才是关键.
- Public arrSP
- Sub TZ_20150913_1()
- Dim arr
- Dim i As Long, j As Long
- Dim D As Object
- Dim t
- t = Time
- Call SP
- Set D = CreateObject("Scripting.Dictionary")
- arr = Sheet2.UsedRange
- For j = 1 To UBound(arrSP)
- If Not D.Exists(arrSP(1)) Then D(arrSP(1)) = 0
- 'SPECIAL STATUS INTO DICTIONARY
- Next j
- For i = 2 To UBound(arr)
- If D.Exists(arr(i, 10)) Then
- 'SPECIAL STATUS
- arr(i, 14) = 0 'D(arr(i, 10))
- 'VALUE=0
- Else
- arr(i, 14) = arr(i, 12) - arr(i, 13)
- 'VALUE=QTY-QTY_REQ
- End If
- Next i
- Sheet1.[A2].Resize(UBound(arr), UBound(arr, 2)) = arr
- Sheet1.[A1] = (Time - t) * 100000
- MsgBox ("DONE")
- End Sub
- Sub TZ_20150913_2()
- Dim arr
- Dim i As Long, j As Long
- Dim t
- t = Time
- Call SP
- arr = Sheet2.UsedRange
- For i = 2 To UBound(arr)
- If funSP(arr(i, 10)) Then
- 'SPECIAL STATUS
- arr(i, 14) = 0 'D(arr(i, 10))
- 'VALUE=0
- Else
- arr(i, 14) = arr(i, 12) - arr(i, 13)
- 'VALUE=QTY-QTY_REQ
- End If
- Next i
- Sheet1.[A2].Resize(UBound(arr), UBound(arr, 2)) = arr
- Sheet1.[A1] = (Time - t) * 100000
- MsgBox ("DONE")
- End Sub
- Function funSP(data) As Boolean
- For i = 1 To UBound(arrSP)
- If data = arrSP(i) Then
- funSP = True
- Exit Function
- End If
- Next i
- End Function
- Sub SP()
- ReDim arrSP(1 To 4)
- arrSP(1) = "Production Open"
- arrSP(2) = "Production Partial"
- arrSP(3) = "Supply Eligible"
- arrSP(4) = "Supply Partial"
- End Sub
复制代码 |
|