将值写入单元格时，VBA停止运行

Question

我正在整理一个有条理的表格中的脏字。当标记行的单元格完成时，此代码停止。你能帮我让它继续循环吗？

Private Sub CommandButton1_Click()    
    Dim sh As Worksheet
    Dim sh7 As Worksheet

    Dim CNAME As String

    Set sh = Worksheets("Sheet6")
    Set sh7 = Worksheets("Sheet7")    
    lr = sh.Cells(Rows.Count, 1).End(xlUp).Row

    For n = 1 To lr
        If InStr(1, sh.Cells(n, 1), "CALL:") = 1 Then
            CNAME = sh.Cells(n, 7).Value    
            Ci = sh.Cells(n + 1, 7).Value    
            Cpd = sh.Cells(n + 1, 7).Value    
        Else
            If InStr(1, sh.Cells(n, 1), "Topic:") = 1 Then
                T = sh.Cells(n, 2)
                Tpd = sh.Cells(n + 1, 2)
                Types = sh.Cells(n + 4, 2)
                DM = sh.Cells(n + 5, 2)
                D = sh.Cells(n + 5, 4)
                OD = sh.Cells(n + 6, 2)
                lr7 = sh7.Cells(Rows.Count, 1).End(xlUp).Row
                sh7.Cells(lr7 + 1, 1).Value = CNAME '********This is the last line it runs.
                sh7.Cells(lr7 + 1, 2).Value = Ci
                sh7.Cells(lr7 + 1, 3).Value = Cpd
                sh7.Cells(lr7 + 1, 4).Value = T
                sh7.Cells(lr7 + 1, 5).Value = Tpd
                sh7.Cells(lr7 + 1, 6).Value = Types
                sh7.Cells(lr7 + 1, 7).Value = DM
                sh7.Cells(lr7 + 1, 8).Value = D
                sh7.Cells(lr7 + 1, 9).Value = OD
            End If  
        End If
    Next n
End Sub

Answer 1

您应该养成定义所有变量并提供默认值的习惯。

编辑:我最初的结论似乎是不正确的。在进一步检查之后，我发现您的代码中可能存在什么问题。在两次尝试获取最后一行时，都使用Rows.Count作为参数。

也许可以改变这些

lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
lr7 = sh7.Cells(Rows.Count, 1).End(xlUp).Row

(请注意，我在第一个参数中使用了sheet变量)

lr = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
lr7 = sh7.Cells(sh7.Rows.Count, 1).End(xlUp).Row