Go through column and replace if containing these values. If not leave as is

Question

I want to go through column L and replace cells containing these text fields to 0. If not I want to leave as is. The code runs but stops at the first #N/A that it encounters.

Sub Drops()
    Dim i&, z&
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual

        With Sheets("Input")
            i = .Cells(Rows.Count, "L").End(xlUp).Row
            For z = i To 2 Step -1
                If (.Cells(z, "L").Value2 Like "*Customer Dropoff*" _
                    Or .Cells(z, "L").Value2 Like "*RE-Ships No pick up charge*" _
                        Or .Cells(z, "L").Value2 Like "*Undeliverable Publication Mail (NO P/U CHARGE)*" _
                            Or .Cells(z, "L").Value2 Like "*RETURNS*" _
                                Or .Cells(z, "L").Value2 Like "*K2 Fed Ex*" _
                                    Or .Cells(z, "L").Value2 Like "*WorldNet Shipping*" _
                                        Or .Cells(z, "L").Value2 Like "*OSM (NO P/U COST)*" _
                                            Or .Cells(z, "L").Value2 Like "*TEST PICK UP*") Then

                    .Cells(z, "L").Value2 = 0
                End If
            Next z
            z = .Cells(Rows.Count, "L").End(xlUp).Row
        End With

        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    MsgBox i - z & " Rows has been changed!"
End Sub

it highlights the whole if, or block – ichoi Dec 22 '15 at 22:19 — ichoi, Dec 22 '15 at 22:19

Tim Williams · Accepted Answer · 2015-12-22T23:27:11.680

Untested:

Sub Drops()
    Dim i As Long, z As Long, arr, v, n As Long
    Dim numEdits As Long

    numEdits = 0
    arr = Array("Customer Dropoff", "RE-Ships No pick up charge", _
                "Undeliverable Publication Mail (NO P/U CHARGE)") 'etc

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    With Sheets("Input")
        i = .Cells(Rows.Count, "L").End(xlUp).Row

        For z = i To 2 Step -1

            v = .Cells(z, "L").Value
            If IsError(v) Then
                'ignore error ?
            Else
                For n = LBound(arr) To UBound(arr)
                    If v Like "*" & arr(n) & "*" Then
                        .Cells(z, "L").Value = 0
                        numEdits = numEdits + 1
                        Exit For
                    End If
                Next n
            End If

        Next z

    End With

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

    MsgBox numedits & " Row(s) changed!"

End Sub

Great works perfectly but the message box does not display number of cells changed. It says 0 when numerous changes were made. Thanks though. — ichoi, Dec 22 '15 at 22:31
It wasn't clear what you intended with that Msgbox. See my updates above. — Tim Williams, Dec 22 '15 at 23:28

Go through column and replace if containing these values. If not leave as is

1 Answers1