VBA:无效的下一个控制变量引用

Question

基本上，我正在尝试使用为了我的目的而剥离并回收的代码来模拟连接结果。但是当脚本尝试处理“Next T”idk 时我遇到了问题，但我已经指示为 Dim - Integer，但这似乎仍然没有解决问题。

代码原始来源:
Concatenate multiple ranges using vba

我在这件作品上遇到了很多问题，因为它似乎是我长期以来一直在尝试将其包含在我的脚本中的唯一内容。在关闭 If、调整 Then 甚至退出循环时出现编译错误。

我认为下一个应该是我最后的担忧。

顺便说一句，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

Answer 1

再试一次......当我仔细查看您的代码时，我实际上使用了一个坏词。

您一直与错误的人群混在一起，并且正在接受一些非常糟糕的代码结构想法。一个 GoTo后跟 Exit For ?后一种说法永远达不到!并跳出一个For循环是一件危险的事情(如果没有错的话)。是的，您仍然需要 Next对于For Each语句(具有匹配的控制参数 - Next T 属于不同的 For 循环，而不是最里面的循环)。

无论如何-我觉得the Cat In The Hat :“这个烂摊子这么大，这么深，这么高——我们不能捡起来，根本没有办法!”。所以我决定给你盖一座新房子。

我认为以下内容可以完成您想做的事情，而且非常优雅。看看它是否有意义，以及是否可以根据您的目的进行调整。我需要去 sleep ，但会在早上看一看，看看你是否从这里弄明白了。

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