How to modify part of a string with VBA

Question

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.

Answer 1

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