需要更正VBA代码。收到错误消息。"For Control Variable already in use"

Question

我在Excel中使用VBA。

我收到一条错误消息，说控制变量已在使用中。当错误弹出时，VBA编辑器中将突出显示第二个For Each r In。有人能帮我纠正这个代码吗。谢谢

Dim ws As Worksheet
Dim r As Range
For Each ws In Worksheets
    If InStr(1, ws.Name, "Wk", 1) > 0 Then
        For Each r In ws.Range("C118:I124")
        For Each r In ws.Range("C163:J168")
        For Each r In ws.Cell(E2, E15, E28, E41, E54, E67, E80)
            r.Formula = Replace(r.Formula, "Wk1", r.Parent.Name)
        Next r
    End If
Next ws
MsgBox "Done"
End Sub

Answer 1

您试图运行三个独立的范围，即连接这些范围。然而，For-Each不是这样工作的，您必须将其分解。

Dim ws As Worksheet
Dim r As Range
For Each ws In Worksheets
    If InStr(1, ws.Name, "Wk", 1) > 0 Then
        For Each r In ws.Range("C118:I124")
            r.Formula = Replace(r.Formula, "Wk1", r.Parent.Name)
        Next r
        For Each r In ws.Range("C163:J168")
            r.Formula = Replace(r.Formula, "Wk1", r.Parent.Name)
        Next r
        Range("E2").Formula = Replace(Range("E2").Formula, "Wk1", Range("E2").Parent.Name)
        Range("E15").Formula = Replace(Range("E15").Formula, "Wk1", Range("E15").Parent.Name)
        Range("E28").Formula = Replace(Range("E28").Formula, "Wk1", Range("E28").Parent.Name)
        Range("E41").Formula = Replace(Range("E41").Formula, "Wk1", Range("E41").Parent.Name)
        Range("E54").Formula = Replace(Range("E54").Formula, "Wk1", Range("E54").Parent.Name)
        Range("E67").Formula = Replace(Range("E67").Formula, "Wk1", Range("E67").Parent.Name)
        Range("E80").Formula = Replace(Range("E80").Formula, "Wk1", Range("E80").Parent.Name)
    End If
Next ws
MsgBox "Done"
End Sub

编辑：已更改以适应注释。它并没有变得越来越漂亮，但恐怕没有办法让调用几个松散的单元格变得更优雅。