Excel VBA：For 循环通过工作表范围进行迭代
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18241986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: For Loop for iteration through Sheet range
提问by user2608147
In the following program, what I am trying to do is scan the "yes" column of a specific range of sheets in a workbook that a user fills out, and wherever the user puts an "x" within that specific "Yes" column range, it will identify the associated item of the question marked in that row and copy the item code associated with that question (e.g. C3) into a Summary page for logging purposes.
在下面的程序中，我想要做的是扫描用户填写的工作簿中特定范围工作表的“是”列，以及用户在该特定“是”列范围内放置“x”的任何位置，它将识别在该行中标记的问题的关联项目，并将与该问题关联的项目代码（例如 C3）复制到摘要页面以进行记录。
The problem is that the code does not copy the item onto the summary page as intended when the for loop iterates through the desired range of sheets. However, if I comment out the for loop code and write Sheets(6).Select instead of Sheets(i).Select, for example, it will copy the "x" marked items onto the summary page for sheet index #6 as intended. This leads me to believe my copy+paste part of the code works (between the while loop statements), but the for loop fails somehow.
问题是当 for 循环遍历所需的工作表范围时，代码没有按预期将项目复制到摘要页面上。但是，如果我注释掉 for 循环代码并编写 Sheets(6).Select 而不是 Sheets(i).Select，例如，它将按预期将“x”标记的项目复制到工作表索引 #6 的摘要页面上. 这让我相信我的代码的复制+粘贴部分有效（在 while 循环语句之间），但 for 循环以某种方式失败。
Can somebody please help me identify the source of the error? I understand that this code is not efficient, such as the excessive use of .select and non-dynamic declarations, but if I wanted to keep as much of the code the same as possible, how could I modify it to make it loop through all the sheets as I intended?
有人可以帮我找出错误的来源吗？我知道这段代码效率不高，例如过度使用 .select 和非动态声明，但如果我想尽可能多地保持代码相同，我该如何修改它以使其循环遍历所有我想要的床单？
Thanks
谢谢
Sub DSR_Autofill()
' Variable Declarations:
Dim x_count As Long 'keeps track of how many "x"s you have
Dim i As Long 'for loop index
Dim n As Long 'while loop index
Dim item_a As String 'Letter part of Item
Dim item_b As String 'Number part of Item
' Variable Initializations:
x_count = 0 'start x count at zero
' Clear Previous Data:
Sheets(2).Range("A25:A29").ClearContents 'Clear Summary Pages before scanning through
Sheets(3).Range("A18:A200").ClearContents
' Main Data Transfer Code:
For i = 5 To i = 20 'Starts at "Process Control" and ends on "Product Stewardship"
Sheets(i).Select 'Select current indexed worksheet and...
Range("D15").Select '...the first item cell in the "Yes" Column
n = 0 'initialize n to start at top item row every time
Do While ActiveCell.Offset(n, -3) <> Empty 'Scan down "YES" column until Item Column (just "A" Column)...
'...has no characters in it (this includes space (" "))
If (ActiveCell.Offset(n, 0) = "x" _
Or ActiveCell.Offset(n, 0) = "X") Then 'If an "x" or "X" is marked in the "YES" column at descending...
'...cells down the column, at an offset specified by the for loop index n
item_a = ActiveCell.Offset(n, -3).Value ' Store Letter value
item_a = Replace(item_a, "(", "") ' Get rid of "(", ")", and " " (space)
item_a = Replace(item_a, ")", "") ' characters that are grabbed
item_a = Replace(item_a, " ", "")
item_b = ActiveCell.Offset(n, -2).Value ' Store number value
item_b = Replace(item_b, "(", "") ' Get rid of "(", ")", and " " (space)
item_b = Replace(item_b, ")", "") ' characters that are grabbed
item_b = Replace(item_b, " ", "")
x_count = x_count + 1 ' increment the total x count
If (x_count > 5) Then ' If there are more than 5 "x" marks...
Sheets("SUMMARY P.2").Activate ' ...then continue to log in SUMMARY P.2 and...
Range("A18").Select ' ...choose "Item" column, first cell
ActiveCell.Offset((x_count - 6), 0).Value = (item_a & item_b)
'Insert concatenated value of item_a and item_b (for example "A" & "1" = "A1")
'at the cells under the "Item" column, indexed by x_count
Else ' If there are less than 5 "x" marks...
Sheets("SUMMARY P.1").Activate ' ...log in SUMMARY P.1 and...
Range("A25").Select ' ...choose "Item" column, first cell
ActiveCell.Offset((x_count - 1), 0).Value = (item_a & item_b)
End If
End If
n = n + 1
Sheets(i).Select 'Return back to current sheet before running again
Range("D15").Select
Loop 'syntax for continuation of while loop
Next i 'syntax for continuation of for loop
If (x_count > 5) Then 'Bring user back to the Summary Page where the last Item was logged
Sheets("SUMMARY P.2").Select
Else
Sheets("SUMMARY P.1").Select
End If
End Sub
回答by tigeravatar
Take out the second "i = " in your For line:
取出 For 行中的第二个“i =”：
For i = 5 To 20
