阻止如果没有结束如果

Question

我对VBA很陌生。我试图计算一个向量的中值。以下代码不断收到关于"阻止if而不结束if"的警告。我试图更改"End IF"的位置，但它导致了另一个警告"Block End IF without IF"。如果您有意见，我们将不胜感激。谢谢

Sub CalculateMedian()
    DoCmd.SetWarnings False
    Dim db As DAO.Database
    Dim onet As DAO.Recordset
    Dim Ocode As String
    Dim ag As DAO.Recordset
    Dim agMedian As Integer
    Set db = CurrentDb
    'select one variable in current database
    Set onet = db.OpenRecordset("SELECT DISTINCT ONetCode FROM Single WHERE LEN(ONetCode)>8")
    Do While Not onet.EOF
        'assigning value to a variable does not need a "SET"
        Ocode = onet.Fields("ONetCode")
        'any data meet the criterion--&Ocode& can vary
        Set ag = db.OpenRecordset("SELECT AG FROM Single WHERE ONetCode='" & Ocode & "' ORDER BY AG")
        'using .recordcount needs to use .movelast first
        ag.MoveLast
        ag.MoveFirst
        If ag.RecordCount Mod 2 = 1 Then
            agMedian = ((ag.RecordCount + 1) / 2)
            thecount = 0
            Do While Not ag.EOF
                thecount = thecount + 1
                If thecount = agMedian Then
                    'inset the result into a new table, and need to create a new table in advance
                    DoCmd.RunSQL ("INSERT INTO PCImedian(onetcode, agMedian) VALUES('" & Ocode & "'," & ag("AG") & ");")
                    Exit Do
        End If
       If ag.RecordCount Mod 2 = 0 Then
            agMedian = ag.RecordCount / 2
            thecount = 0
            Do While Not ag.EOF
                thecount = thecount + 1
                If thecount = agMedian Then
                    m1 = ag("AG")
                ElseIf thecount = agMedian + 1 Then
                    m2 = ag("AG")
                    DoCmd.RunSQL ("INSERT INTO PCImedian(onetcode, agMedian) VALUES('" & Ocode & "'," & ((m1 + m2) / 2) & ");")
                    Exit Do
        End If
    Loop
    DoCmd.SetWarnings True
End Sub

Answer 1

代码缺少多个End If。还有2个Loop语句缺失。

当代码足够复杂，以至于对块结束语句进行排序变得很有挑战性时，请复制该过程，并基本上丢弃除块控制语句之外的所有内容。该方法将其从当前代码中删除。

    Do While Not onet.EOF
        If ag.RecordCount Mod 2 = 1 Then
            Do While Not ag.EOF
                If thecount = agMedian Then
        End If
        If ag.RecordCount Mod 2 = 0 Then
            Do While Not ag.EOF
                If thecount = agMedian Then
                ElseIf thecount = agMedian + 1 Then
        End If
    Loop

这是我对你需要什么的最佳猜测。我在其中几句话后面加了评论，因为这有助于我正确地匹配它们。

    Do While Not onet.EOF
        If ag.RecordCount Mod 2 = 1 Then
            Do While Not ag.EOF
                If thecount = agMedian Then
                End If ' thecount
            Loop ' Not ag.EOF
        End If ' ag.RecordCount Mod 2 = 1
        If ag.RecordCount Mod 2 = 0 Then
            Do While Not ag.EOF
                If thecount = agMedian Then
                ElseIf thecount = agMedian + 1 Then
                End If ' thecount
            Loop ' Not ag.EOF
        End If ' ag.RecordCount Mod 2 = 0
    Loop ' Not onet.EOF

Answer 2

如果在第一个代码块中退出do，则似乎缺少结尾。那里应该有2个，一个用来结束最后一个if语句，一个用于结束第一个块。

Sub CalculateMedian()
    DoCmd.SetWarnings False
    Dim db As DAO.Database
    Dim onet As DAO.Recordset
    Dim Ocode As String
    Dim ag As DAO.Recordset
    Dim agMedian As Integer
    Set db = CurrentDb
    'select one variable in current database
    Set onet = db.OpenRecordset("SELECT DISTINCT ONetCode FROM Single WHERE LEN(ONetCode)>8")
    Do While Not onet.EOF
        'assigning value to a variable does not need a "SET"
        Ocode = onet.Fields("ONetCode")
        'any data meet the criterion--&Ocode& can vary
        Set ag = db.OpenRecordset("SELECT AG FROM Single WHERE ONetCode='" & Ocode & "' ORDER BY AG")
        'using .recordcount needs to use .movelast first
        ag.MoveLast
        ag.MoveFirst
        If ag.RecordCount Mod 2 = 1 Then
            agMedian = ((ag.RecordCount + 1) / 2)
            thecount = 0
            Do While Not ag.EOF
                thecount = thecount + 1
                If thecount = agMedian Then
                    'inset the result into a new table, and need to create a new table in advance
                    DoCmd.RunSQL ("INSERT INTO PCImedian(onetcode, agMedian) VALUES('" & Ocode & "'," & ag("AG") & ");")

                   End If 'ends the If thecount = agMedian if statement -- will continue to iterate until EOF
                    Exit Do 'EOF hit.

        End If 'ends the If ag.RecordCount Mod 2 = 1 block
       If ag.RecordCount Mod 2 = 0 Then
            agMedian = ag.RecordCount / 2
            thecount = 0
            Do While Not ag.EOF
                thecount = thecount + 1
                If thecount = agMedian Then
                    m1 = ag("AG")
                ElseIf thecount = agMedian + 1 Then
                    m2 = ag("AG")
                    DoCmd.RunSQL ("INSERT INTO PCImedian(onetcode, agMedian) VALUES('" & Ocode & "'," & ((m1 + m2) / 2) & ");")
                    Exit Do
        End If 'thecount = agMedian if statement
        End If 'end ag.RecordCount Mod 2 = 0
    Loop
    DoCmd.SetWarnings True
End Sub