|
本帖最后由 mxg825 于 2011-11-8 23:58 编辑
这那个A列除C列 等于整数 要求不明,未添加
- Sub MXG825()
- Dim ARR, CRR, R&, X&, Z1!, JZ!
- Dim JS1!, JS2!, JS3!
- R = Range("B65536").End(xlUp).Row
- Application.ScreenUpdating = False
- Range("E1:E" & R).ClearContents
- ARR = Range("B1").Resize(R, 4)
- Range("A4").Copy
- Range("C1").Resize(R, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
- Range("C1").Resize(R, 1).Sort Key1:=Range("C1")
- CRR = Range("C1:C" & R)
- Range("A1").Select
- JS1 = [A2]: JS2 = [A4]: JS3 = [A6]
- For X = 1 To R
- Z1 = Int(JS1 / (ARR(X, 2) + JS2)) * (ARR(X, 2) + JS2)
- If (JS1 - Z1) <= CRR(1, 1) Then
- ARR(X, 4) = Application.Round((ARR(X, 1) + JS2) / Int(JS1 / (ARR(X, 2) + JS2)) / JS3 * ARR(X, 3), 5)
- Else
- 100:
- JZ = Application.Lookup(JS1 - Z1 - 0.00001, CRR)
- Z1 = Z1 + JZ
- If JS1 - Z1 > CRR(1, 1) Then GoTo 100
- ARR(X, 4) = Application.Round(((ARR(X, 1) + JS2) * (ARR(X, 2) + JS2)) / Z1 / JS3 * ARR(X, 3), 5)
- End If
- Next
- Range("B1").Resize(R, 4) = ARR
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|