CODEWITHSUNDEEP
vbaexcelcriteria
ichoi
ichoi

Reputation: 103

If certain cells meet criteria, change targeted cell in excel vba

If column A contains "RR" and column C is not equal to "memo" and column E is not equal to "Air" or "Printed" then column L=0.

Then followed by If column A contains "RR" and column C is not equal to "memo" and column E is equal to "Air" or "Printed" then column L= is column H*.1.

I believe I am having trouble with equals/not equals.

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

Upvotes: 2

Views: 1030

Answers (1)

user4039065
user4039065

Reputation:

Your logic is slightly flawed when dealing with "and column E is not equal to "Air" or "Printed"". If a cell is not Air it could be Printed. If it is not Printed then it could be Air. You need And here as in "and column E is not equal to "Air" and column E is not equal to "Printed".

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

Using the AutoFilter Method, both operations can be accomplished without declaring a single variable.

Upvotes: 1

Related Questions