標題: [發問] 如何更簡短的撰寫For迴圈 [打印本頁]

---

作者: av8d    時間: 2023-10-15 18:38     標題: 如何更簡短的撰寫For迴圈

本帖最後由 av8d 於 2023-10-15 19:05 編輯

1.     For i = 2 To 21
   
2.         If Workbooks("手冊2.xls").Sheets(1).Cells(i, 3) <> "" Then
   
3.             Workbooks("掛號.xls").Sheets(1).Cells(i + 7, 3) = Workbooks("手冊2.xls").Sheets(1).Cells(i, 4)
   
4.             Workbooks("掛號.xls").Sheets(1).Cells(i + 7, 4) = Workbooks("手冊2.xls").Sheets(1).Cells(i, 16)
   
5.         End If
   
6.     Next
   
7.     For i = 22 To 41
   
8.         If Workbooks("手冊2.xls").Sheets(1).Cells(i, 3) <> "" Then
   
9.             Workbooks("掛號.xls").Sheets(1).Cells(i + 9, 3) = Workbooks("手冊2.xls").Sheets(1).Cells(i, 4)
   
10.             Workbooks("掛號.xls").Sheets(1).Cells(i + 9, 4) = Workbooks("手冊2.xls").Sheets(1).Cells(i, 16)
    
11.         End If
    
12.     Next
    
13.     For i = 42 To 61
    
14.         If Workbooks("手冊2.xls").Sheets(1).Cells(i, 3) <> "" Then
    
15.             Workbooks("掛號.xls").Sheets(1).Cells(i + 11, 3) = Workbooks("手冊2.xls").Sheets(1).Cells(i, 4)
    
16.             Workbooks("掛號.xls").Sheets(1).Cells(i + 11, 4) = Workbooks("手冊2.xls").Sheets(1).Cells(i, 16)
    
17.         End If
    
18.     Next

複製代碼

10/15 19:05 重新撰寫

迴圈i共有300列，請問前輩是否能有比較簡短的寫法，謝謝！

---

作者: av8d    時間: 2023-10-15 19:25

回復 1# av8d

10/15 19:25 解開了，謝謝觀看

1.     a = 1
   
2.     b = 29 '第一次斷點
   
3.     c = 22 '斷點
   
4.     For i = 9 To 300
   
5.         a = a + 1
   
6.         If i = b Then
   
7.             i = i + 2 '斷2格
   
8.             b = b + c
   
9.         End If
   
10.         If Workbooks("手冊2.xls").Sheets(1).Cells(a, 3) <> "" Then
    
11.             Workbooks("掛號.xls").Sheets(1).Cells(i, 3) = Workbooks("手冊2.xls").Sheets(1).Cells(a, 4)
    
12.             Workbooks("掛號.xls").Sheets(1).Cells(i, 4) = Workbooks("手冊2.xls").Sheets(1).Cells(a, 16)
    
13.         End If
    
14.     Next

複製代碼

---

作者: Andy2483    時間: 2023-10-16 14:37

Option Explicit
Sub TEST()
Dim i%, R&, xR1 As Range, xR2 As Range
Set xR1 = Workbooks("手冊2.xls").Sheets(1).Cells
Set xR2 = Workbooks("掛號.xls").Sheets(1).Cells
For i = 9 To 300
   R = i + ((i - 9) \ 20) * 2: If R > 300 Then Exit For
   If xR1(i - 7, 3) <> "" Then xR2(R, 3) = xR1(i - 7, 4): xR2(R, 4) = xR1(i - 7, 16)
Next
Set xR1 = Nothing: Set xR2 = Nothing
End Sub

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)