VBA code to remove text from a column

[jessmendez01]

I need a vba code that will remove any email address in my column H and replace it with the word: SPOT

Can anyone :crying:help me

[SamT]

Sub Spotify()
Dim Cel As Range
'Application.ScreenUpdating = False 'Uncomment after testing

For Each Cel in Intersect(UsedRange, Range("H:H"))
If InStr(Cel.Value, "@") > 0 then Cel.Value = "Spot"
Next

Application.ScreenUpdating = True
End Sub

[jessmendez01]

Its not working ..tells me to debug and when it check its line:

For Each Cel In Intersect(UsedRange, Range("H:H"))

[Omer]

Try
For Each Cel In Intersect(ActiveSheet.UsedRange, Range("H:H"))

[jolivanes]

You could try this also.

Sub jessmendez01()
Dim Cel As Range
Application.ScreenUpdating = False
For Each Cel In Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
If Cel.Value Like "*" & "@" & "*" Then Cel.Value = "Spot"
'If InStr(Cel.Value, "@") > 0 Then Cel.Value = "Spot" '<---- SamT's code works also of course
Next
Application.ScreenUpdating = True
End Sub

[SamT]

Which Code Module did you put the code in?

As I wrote it, the code must be in the Code Module of the sheet it works on.

As Omer changed it, it can be almost anywhere, but in some places, the sub will need the keyword "Public" before it.

Jolivane's code must also be in the WorkSheet's Code Module and you need to edit the ranges from "B" to "H"

[jolivanes]

Jolivane's code must also be in the WorkSheet's Code Module and you need to edit the ranges from "B" to "H"
@SamT
Thank you. That's what you get when you test in a different Column I Guess. Should have changed it.