Forum Discussion

Iron Contributor

Oct 31, 2018

Solved

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:=xlByRow...

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

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
- Haytham Amairah
  Silver Contributor
  Nov 03, 2018
  Hi Juan,
  
  You should declare the second group of typos in a different name, and both of them must be declared as Variant, not as String.
  I've been declared it as String in the previous reply but I quickly updated it.
  
  Sub ReplaceTypos2()
  
  Dim typos1 As Variant
  typos1 = Array("shark", "sharv", "sartv", "satv")
  
      For Each typo In typos1
  
          Selection.Replace What:=typo, Replacement:="sarv", _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
  
      Next typo
  
  Dim typos2 As Variant
  typos2 = Array("marsh", "mars", "marchan", "marshal", "marshall", "mashan", "mershan")
  
      For Each typo In typos2
  
          Selection.Replace What:=typo, Replacement:="marisan", _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
  
      Next typo
  
  End Sub
  
  Hope that helps
  - juan jimenez
    Iron Contributor
    Nov 03, 2018
    i just had to add "next typo 1, 2, etc" and it worked.
    Thank you very much for your quick and useful help.
    Have a nice weekend!