vba Excel 宏生成下标超出范围错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27566793/
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 macro generates Subscript out of Range error
提问by Ogre Ogre
All three sheets will have the same column headings in row 1. On the first and second sheets in the workbook (titled "Monthly" and "Annual" respectively), I am using conditional formatting to color cells in columns Y and AC based upon there values as determined by a formula (Yellow if the calculation returns a value less than 90, Red if the value returned is 0 or a negative number). What I would like a macro to do is to copy the entire row from column A through column AD to a third sheet (titled "Maint Due"). It would also be nice if this process were automated so that anytime the values in columns Y and AC changed in the "Monthly" or "Annual" sheets, the information in the "Maint Due" sheet was automatically updated (but if I have to rerun the macro manually for that to happen it's not a big deal).
所有三张工作表在第 1 行都具有相同的列标题。在工作簿的第一张和第二张工作表上（分别名为“每月”和“每年”），我使用条件格式为 Y 列和 AC 列中的单元格着色基于那里由公式确定的值（如果计算返回的值小于 90，则为黄色，如果返回的值为 0 或负数，则为红色）。我想要一个宏做的是将整行从 A 列到 AD 列复制到第三张表（标题为“维护到期”）。如果这个过程是自动化的，那么任何时候 Y 列和 AC 列中的值在“每月”或“年度”工作表中发生变化，“维护到期”工作表中的信息都会自动更新（但如果我必须手动重新运行宏以实现它'
I've never used the Macro recorder and I can only figure out how to create a macro to copy and paste, so I had no luck there. After doing some more searching and watching some videos I cobbled this together:
我从来没有使用过宏记录器，我只能弄清楚如何创建一个宏来复制和粘贴，所以我在那里没有运气。在进行了更多搜索并观看了一些视频后，我将其拼凑在一起：
Sub Show_on_Maint()
x = 2
'Sets the starting row
Do While Cells(x, 2) <> ""
'Continue to evaluate until a blank cell is reached
If Cells(x, 25) <= 90 Then
'Evaluates the cell in column Y to determine if the value
' is less than or equal to 90
Sheets("Monthly").Rows(x).Copy Sheets("Maint Due").Range("A2")
'Copies the row to the Maint Due sheet
Else
If Cells(x, 29) <= 90 Then
'Evaluates the cell in column AC to determine if the value
' is less than or equal to 90
Sheets("Monthly").Rows(x).Copy Sheets("Maint Due").Range("A2")
'Copies the row to the Maint Due sheet
End If
End If
x = x + 1
Loop
End Sub
When I run/debug it I get a Loop without Do error. I think my logic is sound but I don't have enough experience to figure out why I'm getting that error.
当我运行/调试它时，我得到一个没有 Do 错误的循环。我认为我的逻辑是合理的，但我没有足够的经验来弄清楚为什么我会收到这个错误。
EDIT: Fixed the missing End If before x = x + 1
编辑：修复了在 x = x + 1 之前丢失的 End If
Now I receive a runtime error 9 "Subscript out of range" at: Sheets("Sheet1").Rows(x).Copy Sheets("Sheet6").Range("A2")
现在我在以下位置收到运行时错误 9“下标超出范围”： Sheets("Sheet1").Rows(x).Copy Sheets("Sheet6").Range("A2")
EDIT: Fixed sheet names. Macro now runs without errors but doesn't appear to do anything. Also edited main post for brevity.
编辑：固定工作表名称。宏现在运行没有错误，但似乎没有做任何事情。为简洁起见，还编辑了主要帖子。
回答by Alexander Bell
You code snippet contains multiple error. The first 'Loop without Do error' has been fixed in comments. The second one, 'Subscript out of range' is self-descriptive, so check the maximum value of x+1by adding
您的代码片段包含多个错误。第一个“没有执行错误的循环”已在评论中修复。第二个，“下标超出范围”是自描述的，因此x+1通过添加来检查最大值
Debug.Print x = x + 1
to your loop. Also, make sure that you are not referencing a non-existing Worksheet (i.e. Sheets("Sheet6")) in your copy statement.
到你的循环。另外，请确保您没有Sheets("Sheet6"在复制语句中引用不存在的工作表（即））。
Hope this will help.
希望这会有所帮助。
