我在 VBA 中使用嵌套的 Do While 和 For 回圈遇到了一些麻煩。
本質上，我有兩張桌子。我試圖重復第一個表的每一行，但第二個表中有很多行。第二個表中的行數可能會隨時間變化，因此不幸的是，我無法對其擁有的行數進行硬編碼。
在其當前狀態下，for 回圈僅正確地將我的表格的第一行重復到“Var Admin”作業表上，并且不會對第一個表格的第二行執行相同的操作。
這是我迄今為止嘗試過的：
Sheets("Managers").Select
Range("Table6").Select
managers = ActiveSheet.UsedRange.rows.Count
managers = managers - 1
Set myTable = Worksheets("Var").ListObjects("var_no_format")
my = 1
looper = 1
For Each lr In myTable.ListRows
Do While looper <= managers
lr.Range.rows.Copy Sheets("Var Admin").Range("A" & my)
my = my 1
looper = looper 1
Loop
Next lr
我會很感激我能得到的任何幫助。我不是最擅長在這個網站上提問，所以如果有什么需要澄清的，我會盡快回復你。
uj5u.com熱心網友回復：
我已將值復制到陣列中以提高速度。
Option Explicit
Sub copyrows()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim managers As Long, tbl As ListObject
Dim n As Long, i As Long, r As Long, c As Long
Dim trow As ListRow, ar
With ThisWorkbook
Set ws1 = .Sheets("Managers")
Set ws2 = .Sheets("Var")
Set ws3 = .Sheets("Var Admin")
End With
managers = ws1.ListObjects("Table6").ListRows.Count
Set tbl = ws2.ListObjects("var_no_format")
ReDim ar(1 To managers * tbl.ListRows.Count, 1 To tbl.ListColumns.Count)
i = 1
For r = 1 To tbl.ListRows.Count
For c = 1 To tbl.ListColumns.Count
ar(i, c) = tbl.DataBodyRange(r, c).Value2
For n = 1 To managers - 1
ar(i n, c) = ar(i, c)
Next
Next
i = i managers
Next
' output
ws3.Range("A1").Resize(UBound(ar), UBound(ar, 2)) = ar
MsgBox "Table rows copied for " & managers & " times", vbInformation
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/367852.html