Replace certain values in a column

[larryresick]

Here has got to be a better way to do this. This takes forever and gives me a run time error. I want to change the value of the cell only if it is NOT one of the four IFS. I probably want a find and replace but only if it is on condition it is not one of the four values (Player Tier, Veteran Tier, Preferred Tier, Platinum Tier).



Dim i As Integer


i = 2
Do While i <= ThisWorkbook.ActiveSheet.UsedRange.Rows.Count


If ThisWorkbook.ActiveSheet.Cells(i, 9) = "Player Tier" Then GoTo Line12
If ThisWorkbook.ActiveSheet.Cells(i, 9) = "Veteran Tier" Then GoTo Line12
If ThisWorkbook.ActiveSheet.Cells(i, 9) = "Preferred Tier" Then GoTo Line12
If ThisWorkbook.ActiveSheet.Cells(i, 9) = "Platinum Tier" Then GoTo Line12
ThisWorkbook.ActiveSheet.Cells(i, 9).Value = "Player Tier"


Line12: i = i + 1
Loop


End Sub

Thanks in advance

 

[Joe4]

Try this:

Sub MyMacro()

    Dim i As Integer

    Application.ScreenUpdating = False

    i = 2
    Do While i <= ThisWorkbook.ActiveSheet.UsedRange.Rows.Count
        If (Cells(i, 9) <> "Player Tier") And _
            (Cells(i, 9) <> "Veteran Tier") And _
            (Cells(i, 9) <> "Preferred Tier") And _
            (Cells(i, 9) <> "Platinum Tier") Then
            Cells(i, 9).Value = "Player Tier"
        End If
        i = i + 1
    Loop

    Application.ScreenUpdating = True
    
End Sub

 

[footoo]

Possibly :

Sub FT()
Dim r$
r = Range(Cells(2, 9), Cells(ActiveSheet.UsedRange.Rows.Count, 9)).Address
Range(r) = Evaluate("IF((" & r & "<>""Veteran Tier"")*(" & r & _
    "<> ""Preferred Tier"")*(" & r & "<>""Platinum Tier""),""Player Tier""," & r & ")")
End Sub