if -then- else语句的VBA

Question

下面是我编写的代码，它从单元格Q13中复制值，并将它们粘贴到列E中，直到列表完成(Q13每次都会更新为一个新值)。我有一个条件，如果Q13读取"45“，复制该值并将其粘贴到p列。我知道这是一个if -then- else语句，但我得到了两个不同的编译错误(block if没有end if，if没有block if)，并意识到这很可能是我的语法问题。如果有人能评论一下这篇文章，那就太棒了。

Sub Test()
Dim c As Range
Dim ws As Worksheet: Set ws = Sheet2
Dim range1 As Range: Set range1 = ws.Range("Q13")
Dim Cell As Range

For Each Cell In range1
If Cell.Value = "45" Then
    NextRow = ws.Cells(ws.Rows.Count, "P").End(xlUp).Offset(1, 0).Row
    Cell.Copy ws.Range("P" & NextRow)
    Else
 For Each c In Range("B2:B" & Range("B65536").End(xlUp).Row)
Dim R As Range
Set R = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp) 'last cell in Column E with data
 If Len(R.Value) > 0 Then Set R = R.Offset(1)
R.Value = Worksheets("Sheet2").Range("Q13").Value
Exit For
End If
Next ws
End If

Answer 1

不要把循环变量放在Next关键字后面，它是没用的。它唯一能做的就是让VBA在你使用了错误的循环变量时抛出一个编译器错误--就像这里的情况一样。如果在a循环体中有太多的代码，当你到达底部时，你不再知道自己在哪里，那么循环体做了太多的事情。解决方案是将代码从循环体中提取到更小、更专业的过程中。

如果您不确定如何正确缩进代码，请使用indenter：它会使这种语法错误变得显而易见：

Dim c As Range
Dim ws As Worksheet: Set ws = Sheet2
Dim range1 As Range: Set range1 = ws.Range("Q13")
Dim Cell As Range

For Each Cell In range1
|   If Cell.Value = "45" Then
|   |   NextRow = ws.Cells(ws.Rows.Count, "P").End(xlUp).Offset(1, 0).Row
|   |   Cell.Copy ws.Range("P" & NextRow)
|   Else
|   |   For Each c In Range("B2:B" & Range("B65536").End(xlUp).Row)
|   |   |   Dim R As Range
|   \   |   Set R = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp) 'last cell in Column E with data
\   |\  /   If Len(R.Value) > 0 Then Set R = R.Offset(1)
 \  | \/    R.Value = Worksheets("Sheet2").Range("Q13").Value
  \ / /\    Exit For
   /\/  End If
  / Next
End If

If...Else...End If块以Next终止；内部For Each...Next循环以End If终止，外部For Each...Next循环以End If终止-您需要理清这一点。

Dim c As Range
Dim ws As Worksheet: Set ws = Sheet2
Dim range1 As Range: Set range1 = ws.Range("Q13")
Dim Cell As Range

For Each Cell In range1
    If Cell.Value = "45" Then
        NextRow = ws.Cells(ws.Rows.Count, "P").End(xlUp).Offset(1, 0).Row
        Cell.Copy ws.Range("P" & NextRow)
    Else
        For Each c In Range("B2:B" & Range("B65536").End(xlUp).Row)
            Dim R As Range
            Set R = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp) 'last cell in Column E with data
            If Len(R.Value) > 0 Then Set R = R.Offset(1)
            R.Value = Worksheets("Sheet2").Range("Q13").Value
            Exit For
        Next
    End If
Next