时间戳

首页 > 解决方案 > Excel VBA“如果”、“或”和“和”部分工作

excel - Excel VBA“如果”、“或”和“和”部分工作

问题描述

我的代码循环遍历单元格并根据包含Or函数和And函数的 if 语句填充单元格。

代码部分工作，该Or部分正在工作，但该And部分被忽略，并且仅根据该Or部分填充单元格。

有人可以帮助使And函数中的代码因素而不仅仅是Or部分。

下面是有问题的代码。

Sub MarkUp()
    Worksheets("TO_Project").Activate

    ActiveSheet.Cells(1, 24) = Date
    ActiveSheet.Cells(1, 25).Value = DateAdd("d", 30, ActiveSheet.Cells(1, 24))

    MarkUpLastRow = ActiveSheet.Range("f100000").End(xlUp).Row

    For RowCount = 2 To MarkUpLastRow
        If ActiveSheet.Cells(RowCount, 4) = "01 Prospect" Or ActiveSheet.Cells(RowCount, 4) = "02 Capture" Or ActiveSheet.Cells(RowCount, 4) = "03 Proposal" Or ActiveSheet.Cells(RowCount, 4) = "04 Submitted" And ActiveSheet.Cells(RowCount, 16) < ActiveSheet.Cells(1, 24) Then
            ActiveSheet.Cells(RowCount, 16).Interior.ColorIndex = 37
        End If
    Next RowCount
End Sub

标签： excelvbaif-statement

解决方案

由于运算符优先级，您的条件被解析为：

If _
  (ActiveSheet.Cells(RowCount, 4) = "01 Prospect") _
  Or (ActiveSheet.Cells(RowCount, 4) = "02 Capture") _
  Or (ActiveSheet.Cells(RowCount, 4) = "03 Proposal") _
  Or (ActiveSheet.Cells(RowCount, 4) = "04 Submitted" And ActiveSheet.Cells(RowCount, 16) < ActiveSheet.Cells(1, 24))

显然你的意思是：

If _
  (ActiveSheet.Cells(RowCount, 4) = "01 Prospect" _
   Or ActiveSheet.Cells(RowCount, 4) = "02 Capture" _
   Or ActiveSheet.Cells(RowCount, 4) = "03 Proposal" _
   Or ActiveSheet.Cells(RowCount, 4) = "04 Submitted") _
  And (ActiveSheet.Cells(RowCount, 16) < ActiveSheet.Cells(1, 24))

推荐阅读