vba - Excel VBA 如果然后没有正确评估

Question

我有以下代码，在我看来，IF 语句有问题。工作簿中有两个选项卡，ALLDATA 选项卡和 COMP 选项卡。该宏应该按日期(ALLDATA 选项卡的 A 列)、金额(COMP 选项卡上的单独列，以第 1 行开头)填充收款人列表(ALLDATA 选项卡的第 1 行)。它应该跳过具有 0 或空白值的单元格。所有具有数据的单元格都将具有正值，这就是我检查 > 0 的原因。这是 ALLDATA 选项卡的示例:



这是我希望输出在 COMP 选项卡上的样子:



这是实际输出的样子:



这是我的代码:

Sub Payee_Date()
'
' Payee_Date Macro
'
Application.ScreenUpdating = False

'Select the COMP tab
Sheets("COMP").Activate

'Find the last row of the COMP tab
Dim compLastRow As Long
compLastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Clear the COMP tab
With Sheets("COMP")
    .Rows(2 & ":" & compLastRow).Delete
End With

'Select the ALLDATA tab
Sheets("ALLDATA").Activate

'Find the last row of the ALLDATA tab
Dim alldataLastRow As Long
alldataLastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Find the last column of the ALLDATA tab
Dim alldataLastColumn As Long
With ActiveSheet
    alldataLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

'Loop through the sheet and populate the COMP tab
Dim alldataColNum As Integer
Dim alldataRowNum As Integer
Dim compRowNum As Long
Dim payee As String
Dim day As String
Dim amount As Double

compRowNum = 2

For alldataColNum = 2 To alldataLastColumn Step 1
    For alldataRowNum = 2 To alldataLastRow Step 1
        If Sheets("ALLDATA").Cells(alldataRowNum, alldataColNum).Value > 0 And   IsEmpty(Sheets("ALLDATA").Cells(alldataRowNum, alldataColNum).Value2) = False Then _
            amount = Cells(alldataRowNum, alldataColNum).Value2
            payee = Cells(1, alldataColNum).Value2
            day = Cells(alldataRowNum, 1).Value2

                Sheets("COMP").Activate
                Range("A" & compRowNum).Value = day
                Range("B" & compRowNum).Value = payee
                Range("C" & compRowNum).Value = amount
                Sheets("ALLDATA").Activate

                compRowNum = compRowNum + 1
    Next alldataRowNum
Next alldataColNum

Application.ScreenUpdating = True

'
End Sub

任何有关识别我有缺陷的逻辑/代码的帮助将不胜感激。

Answer 1

您有一个由续行字符引入的非常微妙的错误:

If Sheets("ALLDATA").Cells(alldataRowNum, alldataColNum).Value > 0 And   IsEmpty(Sheets("ALLDATA").Cells(alldataRowNum, alldataColNum).Value2) = False Then **_**

如果测试条件的计算结果为真，则该继续字符会导致“then”部分执行下一条语句，并立即终止 IF block 。控制继续，执行它后面的所有行——那些你打算由'if/then'控制的行。取下尾随的“_”并在最里面的 Next 之前添加一个“End If”，它应该可以解决您的问题。

For alldataRowNum = 2 To alldataLastRow Step 1
    If Sheets("ALLDATA").Cells(alldataRowNum, alldataColNum).Value > 0 And   IsEmpty(Sheets("ALLDATA").Cells(alldataRowNum, alldataColNum).Value2) = False Then 
        amount = Cells(alldataRowNum, alldataColNum).Value2
        payee = Cells(1, alldataColNum).Value2
        day = Cells(alldataRowNum, 1).Value2

            Sheets("COMP").Activate
            Range("A" & compRowNum).Value = day
            Range("B" & compRowNum).Value = payee
            Range("C" & compRowNum).Value = amount
            Sheets("ALLDATA").Activate

            compRowNum = compRowNum + 1
   End If ' Add the IF block closure here
Next alldataRowNum