excel - 通过 VBA 代码生成的示例是重复的

Question

我已经编写了一个代码来生成样本，但是这些行在第一行中不断重复相同的数字。

我附上了一个带有示例和宏的 Excel 电子表格。

我当前的宏：

Sub PSA_Dist_SampleGenerator()

'Step 1: Assigning a variable to copy iterations for the loop function.
Dim Index As Integer 'Index is to ensure loop function runs up to a specified number of runs.


'Step 2: Altering application settings.
'Setting screen updating off for a fast and crash-proof process.
Application.ScreenUpdating = False
'Setting Excel calculations to 'manual' to decrease the time and processing requirements.
Application.Calculation = xlCalculationManual

'Step 3: Delete previously sampled/copied values
Sheets("covariance matrices (2)").Select
Range("Header").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents


'Step 4: The loop that will copy the desired number of trials
Index = 1

Do While Index <= Range("Iteration_Number").Value
Application.StatusBar = "Running simulation number: " & Index & ", out of " & 
Range("Iteration_Number").Value & " simulations."
Calculate
Range("Header").Offset(Index, 0).Value = Range("Sampled_Values").Value
Index = Index + 1
Loop

'Step 5: Resetting application settings.
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

---

下面是一个替代宏，它也重复第一行（代码重复，因为试图压缩它导致没有生成样本）

---

Sub Generate_Samples_PSA()
'
Dim Iterations, n As Integer

Application.ScreenUpdating = False

Iterations = Range("w2").Value

For n = 1 To Iterations

Calculate
Range("K3:K4").Select
Selection.Copy
Range("S4").Select
ActiveCell.Offset(n, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Next n

For n = 1 To Iterations
Calculate
Range("L3:L4").Select
Selection.Copy
Range("U4").Select
ActiveCell.Offset(n, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Next n

For n = 1 To Iterations

Calculate
Range("M3:M4").Select
Selection.Copy
Range("W4").Select
ActiveCell.Offset(n, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Next n

For n = 1 To Iterations

Calculate
Range("N3:N4").Select
Selection.Copy
Range("Y4").Select
ActiveCell.Offset(n, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Next n

End Sub

标签： excelvba

Answer 1

参考第一个代码：该值重复，因为您正在设置所有值以Range("Sampled_Values").Value尝试将行更改为

Range("Header").Offset(Index, 0).Value = Range("Sampled_Values").Offset(Index, 0).Value