|
- Sub sujsbcl()
- rng1 = Range("A2", Cells(Rows.Count, "A").End(3))
- rng2 = Range("F1").CurrentRegion
- rng3 = Range("I1").CurrentRegion
- ReDim brr(1 To UBound(rng1), 1 To 1)
- ReDim crr(1 To UBound(rng1), 1 To 1)
- ReDim drr(1 To UBound(rng1), 1 To 1)
- For i = 1 To UBound(rng1)
- For j = 2 To UBound(rng2)
- If rng1(i, 1) = rng2(j, 1) Then
- brr(i, 1) = rng2(j, 2)
- Exit For
- ElseIf rng1(i, 1) < rng2(j, 1) Then
- brr(i, 1) = rng2(j - 1, 2)
- Exit For
- End If
- Next
- Next
- For i = 1 To UBound(rng1)
- For j = 3 To UBound(rng3, 2) Step 2
- If rng1(i, 1) <= rng3(1, j) Then
- n = Format(rng1(i, 1), "0.000")
- n = Right(n, 2)
- cm = Val(Left(n, 1)): mm = Val(Right(n, 1))
- crr(i, 1) = rng3(cm + 3, j - 1) + rng3(mm + 3, j)
- Exit For
- End If
- Next
- drr(i, 1) = brr(i, 1) + crr(i, 1)
- Next
- Range("B2").Resize(UBound(brr)) = brr
- Range("C2").Resize(UBound(crr)) = crr
- Range("D2").Resize(UBound(drr)) = drr
- End Sub
复制代码 |
|