VBA：下一个控制变量引用无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21693084/
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
VBA: Invalid Next Control Variable Reference
提问by user2988769
Basically, i'm trying to mimic a concatenate result using code i stripped apart and recycled for my purposes. But i'm having problems when the script attempts to process "Next T" idk, but i already indicated as a Dim - Integer, and that still didnt seem to do the trick.
基本上，我试图使用我为了我的目的而剥离和回收的代码来模拟连接结果。但是，当脚本尝试处理“Next T”idk 时，我遇到了问题，但我已经将其表示为 Dim - Integer，但这似乎仍然无法解决问题。
Original source of code: Concatenate multiple ranges using vba
原始代码来源： 使用 vba 连接多个范围
I've been having a lot of problems with this one piece, cause it seems to be the only thing i've actually been trying to include in my script for a long time now. Had compile errors with closing the If, adjusting the Then, and even Exiting the loop.
我在这件作品中遇到了很多问题，因为它似乎是我长期以来一直试图包含在我的脚本中的唯一内容。在关闭 If、调整 Then 甚至退出循环时出现编译错误。
I think the Next should be my final worries.
我认为 Next 应该是我最后的担忧。
Btw, rnumbers is supposed to hold the place of a value/integer, but i'm not entirely sure if that was done correctly either.
顺便说一句， rnumbers 应该保存值/整数的位置，但我也不完全确定这是否正确完成。
rnumbers = Rows(ActiveCell.Range("A3").End(xlDown)) + 3
'or CellCount = ActiveCell.Range("A" & Rows.Count).End(xldown).Row
Do While Rows(ActiveCell.Range("A3").End(xlDown)) > 3
'For Q = 1 To 10 'This provides a column reference to concatenate - Outer For statement
For T = 3 To rnumbers 'This provides a rows reference to concatenate - Inner for statement
For Each Cell In Cells("A" & T) 'provides rows and column reference
If Cell.Value = "" Then
GoTo Line1 'this tells the macro to continue until a blank cell is reached
Exit For
End If
x = x & Cell.Value & Chr(10) 'This provides the concatenated cell value and comma separator
'Next ' this loops the range
Next T 'This is the inner loop which dynamically changes the number of rows to loop until a blank cell is reached
Line1:
On Error GoTo Terminate 'Terminates if there are less columns (max 10) to concatenate
ActiveCell.Value = Mid(x, 1, Len(x) - 1) 'This basically removes the last comma from the last concatenated cell e.g. you might get for a range 2,3,4, << this formula removes the last comma to
'give 2,3,4
ActiveCell.Offset(1, 0).Select 'Once the concatenated result is pasted into the cell this moves down to the next cell, e.g. from F1 to F2
x = "" 'The all important, clears x value after finishing concatenation for a range before moving on to another column and range
'Next Q 'After one range is done the second column loop kicks in to tell the macro to move to the next column and begin concatenation range again
'rnumbers = 0
'Next
Exit Do
'Resume
Terminate:'error handler
采纳答案by Floris
Trying again... when I took a closer look at your code I actually used a Bad Word.
再试一次......当我仔细查看你的代码时，我实际上使用了一个坏词。
You have been hanging with the wrong crowd, and are picking up some really bad code structure ideas. A GoTofollowed by an Exit For? The latter statement can never be reached! And jumping out of a Forloop is a dangerous (if not wrong) thing to do. And yes, you still needed a Nextfor the For Eachstatement (with a matching control argument - the Next Tbelonged with a different For loop, not the innermost one).
你一直在和错误的人群混在一起，并且正在接受一些非常糟糕的代码结构想法。AGoTo后跟一个Exit For? 后一种说法永远达不到！跳出For循环是一件危险的（如果不是错误的）事情。是的，您仍然需要一个NextforFor Each语句（带有匹配的控制参数 -Next T属于不同的 For 循环，而不是最里面的循环）。
Anyway - I felt like the Cat In The Hat: "This mess is so big and so deep and so tall - we cannot pick it up, there is No Way At All!". So I decided to build you a new house instead.
无论如何 - 我感觉就像戴帽子的猫：“这个烂摊子这么大、这么深、这么高——我们拿不起来，根本没有办法！”。所以我决定给你盖一座新房子。
I think the following does what you want to do, and quite elegantly. See if it makes sense, and if you can adapt it for your purpose. I need to go to sleep but will take a look in the morning to see if you figured it out from here.
我认为以下做你想做的事情，而且非常优雅。看看它是否有意义，以及您是否可以根据自己的目的进行调整。我需要去睡觉，但我会在早上看看你是否从这里想通了。
Sub concAll()
Dim allRows As Range, target as range
Dim oneRow
Dim nc as Integer
Set allRows = Range("A3", "J10") ' pick the real range here - dynamically, probably
nc = allRows.Columns.Count ' need this number later to know where to put result
For Each oneRow In allRows.Rows ' loop over one row of the range at a time
Dim s As String
s = "" ' start with empty string
For Each c In oneRow.Cells ' loop over all the cells in the row
If Not IsEmpty(c) Then
s = s & "," & c.Text
Else
Exit For ' done with this row: found empty cell
End If
Next c ' keep looping over the cells...
Set target = oneRow.Cells(1).Offset(0, oneRow.Cells.Count) ' cell where we put result
target.Value = Mid(s, 2) ' put the concatenated value to the right of everything;
' skipping first comma (which came before first text)
Next oneRow ' repeat for all rows in source range
End Sub
回答by user2988769
I'm sorry, i shouldve explained what i was trying to produce than asking to fix something i wanted to do. My experience in vba has been self-taught, and i'm a little new to asking for help.
对不起，我应该解释我想要制作的东西而不是要求修复我想做的事情。我在 vba 方面的经验是自学的，我对寻求帮助有点陌生。
The script Floris produced seemed to have function but not as intended. Turns out what i wrote is a little outdated, and needs to be wiped and restarted. This was actually an old script i started a few months back that worked off of a web-query. But the website went thru some changes and now the script is all over the place.
弗洛里斯制作的剧本似乎有功能，但并不如预期。原来我写的有点过时了，需要擦除并重新启动。这实际上是我几个月前开始的一个旧脚本，它通过网络查询工作。但是网站经历了一些变化，现在脚本到处都是。
the main issue i was having was a compile-error "Invalid Next Control Variable Reference" Which turns out to be caused by an open 'Do while' loop, that doesnt seem to have much of an exit point from the research i looked up. Was supposed to have used another 'If' command instead. At the same time, when attempting to solve that 'Do While' i added an extra 'Next' (cause i thought they were compatible), and it screwed with the script.
我遇到的主要问题是编译错误“无效的下一个控制变量引用”，结果证明这是由打开的“Do while”循环引起的，我查找的研究似乎没有太多退出点。应该使用另一个“If”命令。同时，在尝试解决“Do While”时，我添加了一个额外的“Next”（因为我认为它们是兼容的），并且它与脚本搞砸了。
Hard to explain.. But the 'Do While' i used, i wanted it to combine the values only if the number of values were greater
很难解释......但是我使用的“Do While”，我希望它只有在值的数量更大时才组合这些值
rnumbers = Rows(ActiveCell.Range("A3").End(xlDown)) + 3
'or CellCount = ActiveCell.Range("A" & Rows.Count).End(xldown).Row
Do While Rows(ActiveCell.Range("A3").End(xlDown)) > 3
But instead it was supposed to be
但它应该是
Dim CellCount As Range
CellCount = ActiveCell.Range("A" & Rows.Count).End(xlDown).Row + 2
'cause its the active cell + two additional cells
If CellCount > 3
Which then opens up into the script Floris submitted. (But that failed too, because of what was stated above).
然后打开 Floris 提交的脚本。（但由于上述原因，这也失败了）。
Thanks again, hope that it explains everything... Sorry if i wasted your time with that one Floris, really do appreciate the assistance. Just wish i had asked for the help sooner, would have saved me a lot of frustration that i'm dealing with now. >_>
再次感谢，希望它解释了一切......对不起，如果我在那个 Floris 上浪费了你的时间，真的很感谢你的帮助。只是希望我能早点寻求帮助，这样可以避免我现在正在处理的很多挫折。>_>
