时间戳

首页 > 解决方案 > 用于遍历工作表和粘贴值的计算的宏会产生错误的结果

excel - 用于遍历工作表和粘贴值的计算的宏会产生错误的结果

问题描述

我有一个简单的宏来顺序复制一列输入值，粘贴到作为大型计算一部分的单元格（跨工作簿），然后将输出粘贴到表中（输入值列旁边）。不知何故，如果我手动插入输入值，宏产生的结果与公式产生的值不同。如果我运行宏两次，它确实如此。任何解决此问题的帮助将不胜感激。我的代码如下：

Sub Macro6()
'
' Macro6 Macro
'
Dim a, b, c, d As Integer

Sheets("PPT_US Sensitivity").Range("E6").Value = "YES"

Application.ScreenUpdating = False
Application.CalculateFull
Sheets("PPT_US Sensitivity").Range("H9").Copy
Sheets("Dropdowns").Range("E14").PasteSpecial Paste:=xlPasteValues
Application.CalculateFull
Sheets("Cockpit").Range("AA40:Ad47").Copy
Sheets("Cockpit").Range("Ak40:An47").PasteSpecial Paste:=xlPasteValues

For a = 0 To 22
Sheets("PPT_US Sensitivity").Range("e13").Offset(a, 0).Copy
Sheets("US-specific assumptions").Range("e126").PasteSpecial Paste:=xlPasteValues
Application.CalculateFull
Sheets("Cockpit").Range("AA44").Copy
Sheets("PPT_US Sensitivity").Range("f13").Offset(a, 0).PasteSpecial Paste:=xlPasteValues
Next a

For b = 0 To 22
Sheets("PPT_US Sensitivity").Range("e13").Offset(b, 0).Copy
Sheets("US-specific assumptions").Range("i126").PasteSpecial Paste:=xlPasteValues
Application.CalculateFull
Sheets("Cockpit").Range("AB44").Copy
Sheets("PPT_US Sensitivity").Range("g13").Offset(b, 0).PasteSpecial Paste:=xlPasteValues
Next b

For c = 0 To 22
Sheets("PPT_US Sensitivity").Range("e13").Offset(c, 0).Copy
Sheets("US-specific assumptions").Range("L126").PasteSpecial Paste:=xlPasteValues
Application.CalculateFull
Sheets("Cockpit").Range("AC44").Copy
Sheets("PPT_US Sensitivity").Range("h13").Offset(c, 0).PasteSpecial Paste:=xlPasteValues
Next c

For d = 0 To 22
Sheets("PPT_US Sensitivity").Range("e13").Offset(d, 0).Copy
Sheets("US-specific assumptions").Range("Q126").PasteSpecial Paste:=xlPasteValues
Application.CalculateFull
Sheets("Cockpit").Range("AD44").Copy
Sheets("PPT_US Sensitivity").Range("i13").Offset(d, 0).PasteSpecial Paste:=xlPasteValues
Next d

Application.ScreenUpdating = True
Sheets("PPT_US Sensitivity").Range("E6").Value = "NO"

'
End Sub

标签： excelvba

解决方案

对不起，但我不得不重写你的代码，这样我才能看到发生了什么。这是我最后看到的。

Sub YourMacro()
    ' 092
    
    Dim WsPit   As Worksheet            ' "Cockpit"
    Dim WsPPT   As Worksheet            ' "PPT_US Sensitivity"
    Dim WsAss   As Worksheet            ' "US-specific assumptions"
    Dim C       As Long                 ' loop counter: column
    Dim R       As Long                 ' loop counter: row
    Dim i       As Long                 ' loop counter: iteration
    
    Set WsPit = Sheets("Cockpit")
    Set WsPPT = Sheets("PPT_US Sensitivity")
    Set WsAss = Sheets("US-specific assumptions")
    
    WsPPT.Range("E6").Value = "YES"
    Application.ScreenUpdating = False

    Sheets("Dropdowns").Range("E14").Value = WsPPT.Range("H9").Value
    With WsPit
        .Range("AA40:Ad47").Copy
        .Range("AK40:AN47").PasteSpecial Paste:=xlPasteValues
    End With

    WsPit.Range("AK40:AN47").Value = WsPit.Range("AA40:AD47").Value
    
    For i = 0 To 3
        C = Array(5, 9, 12, 17)(i)         ' specifying columns E, I, L and Q
        For R = 13 To 35
            WsAss.Cells(126, C).Value = WsPPT.Cells(R, "E").Value
            WsPPT.Cells(R, 6 + i).Value = WsPit.Cells(44, 27 + i).Value
        Next R
    Next i
    
    Application.ScreenUpdating = True
    WsPPT.Range("E6").Value = "NO"
End Sub

不幸的是，我的努力没有得到回报。我可以阅读，但不能真正测试，我怀疑单独重写是否能解决任何问题。我的猜测是，Sheets("Dropdowns").Range("E14").Value = WsPPT.Range("H9").Value为验证下拉列表分配一个新值——可能是直接或间接的（如果下拉列表从代码更改的范围中获取其列表）——直到第二次运行才会生效。

'WsAss.Cells(126, C).Value = WsPPT.Cells(R, "E").Value` 快速将不同的值分配给同一个单元格。更改将导致重新计算工作表，而无需额外提示。但是，当 ScreenUpdating 被禁用时，Excel 可能认为计算是不必要的。因此，如果您想要在下一行代码中重新计算的结果，而 Excel 的速度不够快，请通过启用 ScreenUpdating 来减慢宏的执行速度。但是，既然你说第二次运行一切正常，我更喜欢我的第一个猜测。

推荐阅读