我正在嘗試創建一個嵌套回圈以將 Range("E2:E6") 中的值輸入到單元格 ("B3") 和單元格 ("B2") 中的范圍 ("F2:F6") 中的值。然后將 Range("I2:j2") 的結果記錄到 Sheet2。
這個答案（Excel VBA: How to create loop and save output for each value in range?）對第一次解決這個問題有很大幫助，但我已經被困在如何粘貼 25 (5*5) 個可能的結果值上Range("I2:j2") 到 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
uj5u.com熱心網友回復：
盡量避免.Select& .Activate。相反，正確宣告 wb、ws 和 rng，我們也可以不復制/粘貼（如果你只對值感興趣，當然可以）。有關指導，請參閱：如何避免在 Excel VBA 中使用 Select。使用rng1.value = rng2.value. 它要快得多。
我認為您對需要多少個回圈有點困惑。我認為你只需要一個 myRange 和一個嵌套的 myRange2 （都在行上）。
最后，嘗試使用“有意義”的變數名；這可以幫助您的用戶（例如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
uj5u.com熱心網友回復：
對您有用的幾個專案：
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 - 我在粘貼目標范圍內的列號上使用了乘法，這將防止覆寫資料
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/476956.html