Excel 表格问题：怎么使用宏定义把三列表格存放到九列的表格中？

虫友们怎么把三列表格存放到九列的表格中，使用的宏定义。。第一次写好像有问题。。弄不出来。

像这样，三列数，存到九列中

1. Sub FillTable()
   
2.     Dim ws As Worksheet
   
3.     Set ws = ActiveSheet '或者指定工作表，例如Set ws = Worksheets("Sheet1")
   
4.    
   
5.     Dim col1 As Range, col2 As Range, col3 As Range
   
6.     Set col1 = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).row)
   
7.     Set col2 = ws.Range("B1:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).row)
   
8.     Set col3 = ws.Range("C1:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).row)
   
9.    
   
10.     Dim row As Long, col As Long
    
11.     row = 1
    
12.     col = 5
    
13.    
    
14.     Dim i As Long, j As Long, m As Long
    
15.     For i = 1 To 221
    
16.         For j = 1 To 221
    
17.             For m = 1 To 221
    
18.             ws.Cells(row, col).Value = col1.Cells(i).Value
    
19.             col = col + 1
    
20.             ws.Cells(row, col).Value = col2.Cells(j).Value
    
21.             col = col + 1 '每次填充三列，列数+3
    
22.              ws.Cells(row, col).Value = col3.Cells(m).Value
    
23.             col = col + 1
    
24.             If col > 13 Then '如果超过了9列，换行继续填充
    
25.                 row = row + 1
    
26.                 col = 5
    
27.             End If
    
28.         Next m
    
29.         Next j
    
30.     Next i
    
31.    
    
32. End Sub
    

复制代码

应该只用 1 个 for 循环吧?

1. Sub FillTable()
   
2.     Dim ws As Worksheet
   
3.     Set ws = ActiveSheet '或者指定工作表，例如Set ws = Worksheets("Sheet1")
   
4.    
   
5.     Dim col1 As Range, col2 As Range, col3 As Range
   
6.     Set col1 = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).row)
   
7.     Set col2 = ws.Range("B1:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).row)
   
8.     Set col3 = ws.Range("C1:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).row)
   
9.    
   
10.     Dim row As Long, col As Long
    
11.     row = 1
    
12.     col = 5
    
13.    
    
14.     Dim i As Long, j As Long, m As Long
    
15.     For i = 1 To col1.Rows.Count '不一定是221, 根据实际长度更改
    
16.         ws.Cells(row, col).Value = col1.Cells(i).Value
    
17.             col = col + 1
    
18.             ws.Cells(row, col).Value = col2.Cells(i).Value
    
19.             col = col + 1 '每次填充三列，列数+3
    
20.              ws.Cells(row, col).Value = col3.Cells(i).Value
    
21.             col = col + 1
    
22.             If col > 13 Then '如果超过了9列，换行继续填充
    
23.                 row = row + 1
    
24.                 col = 5
    
25.             End If
    
26.     Next i
    
27.    
    
28. End Sub

复制代码

xjm 发表于 2023-4-23 21:16
应该只用 1 个 for 循环吧?

感谢虫友，突然明白了。