So for example (the Then statement isn't correct, I'm trying to figure out what to put there):
For i = 1 to 20
If Instr(1, Cells(i, "A"), "@goggle.com") > 0 Then
"@goggle.com" = "@google.com"
Next i
So let's say the email "[email protected]" is in cell A2, so Instr finds "@goggle.com" in A2. If I want to change the @goggle.com to @google.com, how would I do that?
My goal is to find common misspellings of email domains and fix them.
To fix your code you can use the Replace function, as mentioned
For i = 1 To 20
If InStr(1, Cells(i, "A"), "@goggle.com") > 0 Then
Cells(i, "A") = Replace(Cells(i, "A"), "@goggle.com", "@google.com")
End If
Next
but to be more efficient about all replacements use the Range().Replace method for a list of values and replacements:
Option Explicit
Public Sub fixColumnSpelling()
Const FIND_LIST As String = "@goggle.com @yahho.com @test1.com"
Const REPL_LIST As String = "@google.com @yahoo.com @test2.com"
Dim totalItems As Long, i As Long, findItems As Variant, replItems As Variant
findItems = Split(FIND_LIST)
replItems = Split(REPL_LIST)
totalItems = UBound(findItems)
For i = 0 To totalItems 'bulk replecements in col A
ActiveSheet.UsedRange.Columns(1).Replace _
What:=findItems(i), _
Replacement:=replItems(i), _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False
Next
End Sub
Look into the Replace Statement.
In this instance:
Replace(Cells(i, "A"), "@google.com", "@gmail.com")
Basically the formula says, in this string `Cells(i,"A") find "@google.com" and replace it with "@gmail.com"
With this you will not need the if statement as the replace function will not replace anything that it does not find. In other words, if "@google.com" is not found in the string it moves on and returns nothing.
