vba - 如果某些单元格符合条件，请更改 excel vba 中的目标单元格

Question

如果 A 列包含“RR”且 C 列不等于“备忘录”且 E 列不等于“Air”或“Printed”，则 L=0。

然后如果 A 列包含“RR”且 C 列不等于“备忘录”且 E 列等于“Air”或“Printed”，则 L= 列是 H*.1 列。

我相信我遇到了等于/不等于的问题。

Sub RRClean()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim myString As String
RowCount = WorksheetFunction.CountA(range("A:A"))

For i = 2 To RowCount
    myString = Trim(Cells(i, 2).Value)
    If InStr(myString, "RR") > 0 And .cell(i, 3) <> "Memo" And .cell(i, 7) <> "Air" Or .cell(i, 7) <> "Printed" Then
        Cells(i, 12).Value = 0
    End If
Next

For i = 2 To RowCount
    myString = Trim(Cells(i, 2).Value)
    If InStr(myString, "RR") > 0 And .cell(i, 3) <> "Memo" And .cell(i, 7) = "Air" Or .cell(i, 7) = "Printed" Then
        Cells(i, 12).Value = cell(i, 8) * 0.1
    End If
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Answer 1

在处理“并且 E 列不等于“Air”或“Printed””时，您的逻辑略有缺陷。如果单元格不是 Air，则可以打印。如果它不是打印的，那么它可能是空气。您需要和此处如“且 E 列不等于“空气”且 E 列不等于“打印”。

Sub Cmemo()
    With Worksheets("Sheet4")    '<~~ SET THIS WORKSHEET REFERENCE PROPERLY!!
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            .AutoFilter Field:=1, Criteria1:="*RR*"
            .AutoFilter Field:=3, Criteria1:="<>memo"
            .AutoFilter Field:=5, Criteria1:="<>Air", _
                        Operator:=xlAnd, Criteria2:="<>Printed"
            If CBool(Application.Subtotal(103, .Offset(1, 0).Cells)) Then
                With .Resize(.Rows.Count - 1, 1).Offset(1, 11)
                    .SpecialCells(xlCellTypeVisible) = 0
                End With
            End If
            .AutoFilter Field:=5, Criteria1:="Air", _
                        Operator:=xlOr, Criteria2:="Printed"
            If CBool(Application.Subtotal(103, .Offset(1, 0).Cells)) Then
                With .Resize(.Rows.Count - 1, 1).Offset(1, 11)
                    .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=rc[-4]/10"
                End With
            End If
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

使用 AutoFilter Method ，这两个操作都可以在不声明单个变量的情况下完成。