visual basic and "OR" command

Dear friends,

I am trying to set up an instruction in Visual Basic to replace error in a text.

Selection.Replace What:="taic", Replacement:="taichi", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

However I would like to replace with the same instruction several mistakes like "taic" OR "taihi" OR "tachi" OR etc.

Could someone tell me how to write properly all within this instruction?

Thank you, Juan

Macros and VBA

Haytham Amairah

Oct 31, 2018

Hi Juan,

I suggest this code:

Sub ReplaceTypos()
 
Dim typos As Variant
typos = Array("taic", "taihi", "tachi")

    For Each typo In typos
    
        Selection.Replace What:=typo, Replacement:="taichi", _
              LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
              SearchFormat:=False, ReplaceFormat:=False
    
    Next typo

End Sub

This code creates a list of typos and stores it in an array named (typos).

Then it will loop through each typo in typos array and apply the replacement on each one of them.

If you have other typos just expand the typos array list in the code as the syntax below.

typos = Array("typo 1", "typo 2", ...)

Hope that helps

17 Replies

Haytham Amairah
Silver Contributor
Oct 31, 2018
Hi Juan,

I suggest this code:
Sub ReplaceTypos()

Dim typos As Variant
typos = Array("taic", "taihi", "tachi")

    For Each typo In typos

        Selection.Replace What:=typo, Replacement:="taichi", _
              LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
              SearchFormat:=False, ReplaceFormat:=False

    Next typo

End Sub

This code creates a list of typos and stores it in an array named (typos).
Then it will loop through each typo in typos array and apply the replacement on each one of them.

If you have other typos just expand the typos array list in the code as the syntax below.
typos = Array("typo 1", "typo 2", ...)

Hope that helps
- juan jimenez
  Iron Contributor
  Nov 01, 2018
  Very helpful. thank you very much for your quick answer!
  - juan jimenez
    Iron Contributor
    Nov 03, 2018
    hello again
    
    i have tried to do two one after the other but it does not work... can you help me again?
    
    Dim types As String
    typos = Array("shark", "sharv", "sartv", "satv")
    For Each typo In typos
    Selection.Replace What:=typo, Replacement:="sarv", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Next typo
    Dim types As String
    typos = Array("marsh", "mars", "marchan", "marshal", "marshall", "mashan", "mershan")
    For Each typo In typos
    Selection.Replace What:=typo, Replacement:="marisan", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Next typo

Forum Discussion

visual basic and "OR" command

17 Replies

Resources