Excel VBA:创建嵌套循环并保存range中每个值的输出

Question

我试图创建一个嵌套循环，将范围("E2:E6")中的值输入到单元格("B3") &单元格("B2")中范围("F2:F6")的值。然后记录从Range(';I2:j2 ';)到Sheet2的结果。

这个答案(Excel VBA:如何创建循环并保存范围内每个值的输出?)在第一次解决问题时非常有帮助，但我已经卡住了，因为我如何将范围("I2:j2")的25(5*5)可能的结果值粘贴到Sheet2

任何帮助都非常感谢!

Sub Nested_Loop()
'
'
'

gg = 1
Dim myRange As Range
Dim myRange2 As Range
Dim i As Long, j As Long, h As Long

Worksheets("Sheet1").Activate
Set myRange = Range("E2:E6")
Set myRange2 = Range("F2:F6")
For h = 1 To myRange2.Rows.Count
For i = 1 To myRange.Rows.Count
For j = 1 To myRange.Columns.Count
myRange.Cells(i, j).Select
Selection.Copy
Range("B3").Select
ActiveSheet.Paste
myRange2.Cells(h, j).Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Range("I2:j2").Select
Application.CutCopyMode = False
Selection.Copy
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Cells(i + 1, j + gg).Select     'I want to paste all 25 values (5 possible inputs for each variable(2)). Currently the loop only prints 5 results and then pastes over itself
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next j
Worksheets("Sheet1").Activate
Next i
Worksheets("Sheet1").Activate
Next h
End Sub 

Answer 1

尽量避免使用.Select&.Activate。相反，正确地声明wb、ws和rng，我们也可以不复制/粘贴(如果你只对值感兴趣，当然)。有关指导，请参见:如何避免在Excel VBA中使用选择。使用rng1.value = rng2.value。它快多了。

我想你对你需要多少个循环有点困惑。我认为你只需要一个myRange和一个嵌套的myRange2(都在行上)。

最后，试着使用"sensical"变量名;这有助于您的用户(例如counter而不是gg)。

下面的代码应该可以工作。(也许是一个很好的挑战，看看你是否可以简单地包括计算，显然在ws.Range("I2:J2")内的代码…)

Sub Nested_Loop()
Dim wb As Workbook
Dim ws As Worksheet, ws2 As Worksheet

Dim myRange As Range, myRange2 As Range, destRange As Range

Dim i As Long, j As Long, counter As Long

Set wb = ActiveWorkbook

Set ws = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

Set myRange = ws.Range("E2:E6")
Set myRange2 = ws.Range("F2:F6")

'set destination range; "B2" as start inferred from original code
Set destRange = ws2.Range("B2").Resize(25, 2) 'i.e. .Resize(5*5=25rows,1+1=2cols)

counter = 0

'loop first range = 1 to 5
For i = 1 To myRange.Rows.Count

'nested -> 5*5
For j = 1 To myRange2.Rows.Count

'use .value = .value instead of copy/paste
ws.Range("B3").Value = myRange.Cells(i).Value
ws.Range("B2").Value = myRange2.Cells(j).Value

'with .value = .value we don't need to leave the activeworksheet
Range(destRange.Cells(1 + counter, 1), destRange.Cells(1 + counter, 2)).Value = ws.Range("I2:J2").Value

'increment counter to go to next row in destRange
counter = counter + 1

Next j
Next i
End Sub

Answer 2

可以为你工作的几个项目:

dim ws1 as worksheet
set ws1 = thisWorkbook.Sheets(1)
dim ws2 as worksheet
set ws2 = thisWorkbook.Sheets(2)
dim rowNum as long
for rowNum = 1 to 5
dim colNum as long
for colNum = 1 to 5
With ws1
.Cells(rowNum,colNum).Copy .Cells(2,2)
.Range(.Cells(2,9),.Cells(2,9+1)).Copy ws2.Range(ws2.Cells(rowNum,colNum*2),ws2.Cells(rowNum,colNum*2+1)
End With
next colNum
next rowNum

• 使用有意义的变量;现在让它成为一种习惯，这样你就不会纠正a, J, K, I等，在未来的道路上
• 限定您的所有范围;注意ws2上的目的地与With ws1
相比要复杂得多
• 我在粘贴目标范围内的列号上使用乘法，这将防止覆盖数据