时间戳

首页 > 解决方案 > 直到单元格值介于 -1 和 1 之间

excel - 直到单元格值介于 -1 和 1 之间

问题描述

又是我......在同一个项目上工作但有不同的问题！

我有 3 个值（计算但我只需要结果值），其中只有 1 个需要插入到我的摊销计划的第一个单元格中。插入值后，最后一个单元格应等于 0 或接近它。

目前，我的循环一直持续到永恒的尽头。

Dim LastCell As Range: Set LastCell = Destination.Offset(9, 12).End(xlDown) 'Last cell of
'the amort schedule, should be 0 or close to it at end of term. Destination is a named cell,
'I'm getting better at avoiding ActiveCell!

Dim PV1 As Double: PV1 = Destination.Offset(0, 12) '2440754.76
Dim PV2 As Double: PV2 = Destination.Offset(1, 12) '2400379.97 This is the value in my 
'current scenario that I need, but it may not always be the case
Dim PV3 As Double: PV3 = Destination.Offset(2, 12) '2429942.76

Dim UseCalc As Range: Set UseCalc = Destination.Offset(9, 12) 'First cell of the amort schedule

Do Until (LastCell.Value >= -1 And LastCell.Value <= 1)
    UseCalc = PV1
    UseCalc = PV2 'When it gets to this point, I can see the value in LastCell
    'becomes 1.0000000000001839362E-02 which SHOULD trip the between -1 and 1 threshold,
    'but I'm sure something in the variables I'm setting are going wacky
    UseCalc = PV3
Loop

我假设我使用的变量组合不正确，这就是为什么它没有触发“直到”，但我已经尝试了我能想到的每一个合理的组合。提前致谢！

标签： excelvba

解决方案

你可以这样做：

Dim LastCell As Range, UseCalc As Range
Dim arrPV(1 to 3) As Double, x as long

Set LastCell = Destination.Offset(9, 12).End(xlDown)
Set UseCalc = Destination.Offset(9, 12)

arrPV(1) = Destination.Offset(0, 12) '2440754.76
arrPV(2) = Destination.Offset(1, 12) '2400379.97
arrPV(3) = Destination.Offset(2, 12) '2429942.76

for x = 1 to ubound(arrPV)
    UseCalc.Value = arrPV(x)
    If LastCell.Value >= -1 And LastCell.Value <= 1 then exit for
next x

推荐阅读