主页 / user-14116785

DR21's questions

Martin Hope
DR21
Asked: 2024-11-08 05:21:45 +0800 CST
  • 6

我无法创建一个有效的代码，该代码循环并返回从特定单元格开始的 7 种场景的结果，并且让每种场景返回到特定单元格正下方的单元格中。

谢谢您的帮助，并且对我的菜鸟技术水平表示歉意。

我正在运行的长格式脚本是这样的：

Sub Macro1()

    Dim X As Worksheet
    Dim Y As Worksheet
    Set X = Sheets("Scenarios")
    Set Y = Sheets("Portfolio Model")
    
    'Run Flat Scenarios
    X.Select
    Range("M2").Select
    If Range("M2") = "N" Then Range("M2").Value = "Y" Else Range("M2").Value = "Y"
                
    '#1 Flat Scenario
    Y.Select
    Range("GO8").Select
        Selection.Copy
    Range("G3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Calculate
    Range("GK5").Select
        Selection.Copy
    Range("GP8").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
                
    '#2 Flat Scenario
    Y.Select
    Range("GO9").Select
        Selection.Copy
    Range("G3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Calculate
    Range("GK5").Select
        Selection.Copy
    Range("GP9").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
                
    '#3 Flat Scenario
    Y.Select
    Range("GO10").Select
        Selection.Copy
    Range("G3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Calculate
    Range("GK5").Select
        Selection.Copy
    Range("GP10").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
                
    '#4 Flat Scenario
    Y.Select
    Range("GO11").Select
        Selection.Copy
    Range("G3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Calculate
    Range("GK5").Select
        Selection.Copy
    Range("GP11").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    
    '#5 Flat Scenario
    Y.Select
    Range("GO12").Select
        Selection.Copy
    Range("G3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Calculate
    Range("GK5").Select
        Selection.Copy
    Range("GP12").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    
    '#6 Flat Scenario
    Y.Select
    Range("GO13").Select
        Selection.Copy
    Range("G3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Calculate
    Range("GK5").Select
        Selection.Copy
    Range("GP13").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    
    '#7 Flat Scenario
    Y.Select
    Range("GO14").Select
        Selection.Copy
    Range("G3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Calculate
    Range("GK5").Select
        Selection.Copy
    Range("GP14").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    
End Sub

这是我为了让该脚本更高效并尝试循环运行所采取的措施：

Sub Macro2()

    Dim X As Worksheet
    Dim Y As Worksheet
    Set X = Sheets("Scenarios")
    Set Y = Sheets("Portfolio Model")
    
    'Run Flat Scenarios
    X.Select
    Range("M2").Select
    If Range("M2") = "N" Then Range("M2").Value = "Y" Else Range("M2").Value = "Y"
            
    Dim j As Variant
    Dim jArray As Variant
    jArray = Array(0.085, 0.0875, 0.09, 0.0925, 0.095, 0.0975, 0.01)

    Dim i As Variant
    Dim iArray As Variant
    iArray = Array(1, 2, 3, 4, 5, 6, 7)
    
    For Each i In iArray
        Range("GK5").Copy
        Range("GP" & 7 + i).PasteSpecial xlValues
    
        For Each j In jArray
            Range("G3").Value = j
            Calculate
        Next
    Next

End Sub
arrays