下一个没有 For VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14093853/
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
Next without For VBA
提问by kusold
I keep getting a "compile error: next without For" when I try to run this code. However, after checking everything over multiple times, I do not see how it does not recognize their presences. This is my first VBA code, so any help would be greatly appreciated.
当我尝试运行此代码时，我不断收到“编译错误：next without For”。但是，在多次检查所有内容后，我看不出它是如何不识别它们的存在的。这是我的第一个 VBA 代码，所以任何帮助将不胜感激。
Sub Naming()
'
' Naming Macro
' Assigns a category name in a cell based on values in a cell one column over
'
Dim number As Double
For i = 9 To 200
number = Cells(i, 3).Value
If number = 0 Then
GoTo Line1
Else
If number <= 199999 And number > 0 Then
Cells(i, 2) = "EP-GEARING"
Else
If number <= 399999 And number > 199999 Then
Cells(i, 2) = "DRIVES"
Else
If number <= 499999 And number > 399999 Then
Cells(i, 2) = "FLOW"
Else
If number <= 599999 And number > 499999 Then
Cells(i, 2) = "SPARES"
Else
If number <= 699999 And number > 599999 Then
Cells(i, 2) = "REPAIR"
Else
If number <= 799999 And number > 699999 Then
Cells(i, 2) = "FS"
Else
If number <= 899999 Then
Cells(i, 2) = "GC-GEARING"
Else
GoTo Line1
Line1:
End If
Next i
End Sub
回答by GSerg
ElseIfis one word in VB.
ElseIf是VB中的一个词。
If number = 0 Then
'Do nothing
ElseIf number <= 199999 And number > 0 Then
Cells(i, 2) = "EP-GEARING"
ElseIf number <= 399999 And number > 199999 Then
...
Else
'Do nothing
End If
However, Select Casewould fit better here:
但是，Select Case这里更适合：
Select Case number
Case 0
'Do nothing
Case 1 To 199999
Cells(i, 2) = "EP-GEARING"
Case 200000 To 399999
...
Case Else
'Do nothing
End Select
回答by Robert Harvey
Your code should look like this:
您的代码应如下所示：
Sub Naming()
'
' Naming Macro
' Assigns a category name in a cell based on values in a cell one column over
'
Dim number As Double
For i = 9 To 200
number = Cells(i, 3).Value
If number <= 199999 And number > 0 Then
Cells(i, 2) = "EP-GEARING"
ElseIf number <= 399999 And number > 199999 Then
Cells(i, 2) = "DRIVES"
ElseIf number <= 499999 And number > 399999 Then
Cells(i, 2) = "FLOW"
ElseIf number <= 599999 And number > 499999 Then
Cells(i, 2) = "SPARES"
ElseIf number <= 699999 And number > 599999 Then
Cells(i, 2) = "REPAIR"
ElseIf number <= 799999 And number > 699999 Then
Cells(i, 2) = "FS"
ElseIf number <= 899999 Then
Cells(i, 2) = "GC-GEARING"
End If
Next i
End Sub
The problem with your code as originally written is that, regardless of the Else clauses, the compiler still expects an End If for every If, and is getting confused because they are not there. The single keyword ElseIfonly requires one End Ifstatement at the end.
您最初编写的代码的问题在于，无论 Else 子句如何，编译器仍然希望每个 If 都有一个 End If，并且因为它们不存在而感到困惑。single 关键字最后ElseIf只需要一个End If语句。
Goto's are seldom advisable. 99 percent of the time, there's a better and cleaner way to write it, without using a Goto.
很少建议使用 Goto。99% 的情况下，有一种更好、更简洁的方式来编写它，而无需使用 Goto。
回答by Daniel
The other answers indicate how you could fix your Ifstatement so that VBA recognizes your Forand Nextpair up.
其他答案表明您可以如何修复您的If语句，以便 VBA 识别您For并Next配对。
Now, personally, I would suggest using Select Caseas GSerg indicated, if your loop were necessary.
现在，就个人Select Case而言，如果您的循环是必要的，我建议按照 GSerg 的指示使用。
But here is probably what I would do. In Cell B9place the following formula: =IF(C9=0,"",IF(C9<=199999,"EP-GEARING",IF(C9<=399999,"DRIVES",IF(C9<=499999,"FLOW",IF(C9<=599999,"SPARES",IF(C9<=699999,"REPAIR",IF(C9<=799999,"FS",IF(C9<=899999,"GC-GEARING",""))))))))then copy it down where you need it.
但这可能是我会做的。在单元格中B9放置以下公式：=IF(C9=0,"",IF(C9<=199999,"EP-GEARING",IF(C9<=399999,"DRIVES",IF(C9<=499999,"FLOW",IF(C9<=599999,"SPARES",IF(C9<=699999,"REPAIR",IF(C9<=799999,"FS",IF(C9<=899999,"GC-GEARING",""))))))))然后将其复制到您需要的位置。
Or if you want to do it with code you could replace your whole sub with no looping I could have written this as a 1 liner, but I wanted it to be legible:
或者如果你想用代码来做，你可以用没有循环的方式替换你的整个子我可以把它写成一个 1 liner，但我希望它清晰易读：
Sub Naming()
'
' Naming Macro
' Assigns a category name in a cell based on values in a cell one column over
Dim theRange As Range
Set theRange = Range(Cells(9, 2), Cells(200, 2))
theRange.Value = "=IF(RC[1]=0,""""," & _
"IF(RC[1]<=199999,""EP-GEARING""," & _
"IF(RC[1]<=399999,""DRIVES""," & _
"IF(RC[1]<=499999,""FLOW""," & _
"IF(RC[1]<=599999,""SPARES""," & _
"IF(RC[1]<=699999,""REPAIR""," & _
"IF(RC[1]<=799999,""FS""," & _
"IF(RC[1]<=899999,""GC-GEARING"",""""))))))))"
'Optional if you want only the values without the formula, uncomment next line
'theRange.Value = theRange.Value
Set theRange = Nothing
End Sub
It is generally faster and cleaner to solve things like this using Excel formulas rather than writing out the logic in VBA and looping through cells.
使用 Excel 公式解决此类问题通常更快、更清晰，而不是在 VBA 中写出逻辑并循环遍历单元格。
