VBA Code: If not in column A then Replace

[murthysri272]

Hi,
I am looking for little help in VBA code for below scenario:

I have a column E in sheet1. It has Multiple values from cell E1 to E3000 (Values like DEPT, AGENT, Etc..).
Values in Column E (E1 to E3000) Other than DEPT, AGENT should replace with PRODUCT.


Regards,
Sri

[SamT]

Sub VBAX_SamT_ReplaceIfnot()
Dim Cel as Range
With Sheets("Sheet1")
For Each Cel in Range(.Range("E1"), .Cells(Rows.Count, "E").End(xlUp))
IF Cel <> "DEPT" And Cel <> "AGENT" Then Cel = "PRODUCT"
Next
End With
End sub

Changed OR to AND

[jolivanes]

Or maybe without looping

Sub Without_Looping()
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.AutoFilter Field:=5, Criteria1:="<>DEPT", Operator:=xlAnd, Criteria2:="<>AGENT"
.Range("E1:E" & ActiveSheet.UsedRange.Rows.Count).SpecialCells(12).Value = "PRODUCT"
.CurrentRegion.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

[SamT]

OR should be AND in my post. Would you believe that I've been using Boolean logic since 1969 and I still have to think about it three times. Or more.

[jolivanes]

@ SamT
That change should make the OP happier

[murthysri272]

Thank you.
Its working fine.



Thanks and Regards,
Sri