visual basic and "OR" command

[juan jimenez]

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

[#2 · Accepted solution]

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

[#3]

Very helpful. thank you very much for your quick answer!

[#4]

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

[#5]

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

[#6]

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!

[#7]

Dear Haytam,

Unfortunately what I have tried to develop myself did not work.

It is only recognizes the first word. Can you help?

Dim typos1 As Variant
typos = Array("pipí", "bibi", "pi pi")
For Each typo1 In typos
Selection.Replace What:=typo, Replacement:="pipi", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo1
Dim typos2 As Variant
typos = Array("shark", "sharv", "sartv", "satv")
For Each typo2 In typos
Selection.Replace What:=typo, Replacement:="sarv", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo
Dim typos3 As Variant
typos = Array("marsan", "marchan", "marshal", "marshall", "mashan", "mershan")
For Each typo3 In typos
Selection.Replace What:=typo, Replacement:="marshan", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo3
Dim typos4 As Variant
typos = Array("findu", "facebook")
For Each typo4 In typos
Selection.Replace What:=typo, Replacement:="fidu", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next typo4

[#8]

Hi Juan,

This is maybe a bit confusing at first glance, but it will be easy if you learned the basics of VBA.

This a delaration statement:

Dim typos1 As Variant

And this an assignment statement:
typos1 = Array("pip?", "bibi", "pi pi")

If you declare a variable such as (typos1), you should assign something to this variable!

And this what you didn't do!

You declared typos1, typos2, typos3, and typos4, but you didn't assign anything to these variables!

The assignment statement in each part is assigned to the same undeclared variable (typos).

After you declared typos1 (Dim typos1 as Variant), you should follow it by:

typos1 = Array("pipí", "bibi", "pi pi") 

and so on...

With regards to the variable after the For Each, What:= and Next, please note that I didn't change it in each block, it's just typo as the previous reply.

The reason is that each For...Each block is independent of the other, so there is no need to change the name of this variable because it's defined only at the block level.

Also, you need to put the variable that you've declared after (In) in the loop statement

This your code before adjustment:

Sub ReplaceTypos()

Dim typos1 As Variant
typos = Array("pip?", "bibi", "pi pi")

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

Dim typos2 As Variant
typos = Array("shark", "sharv", "sartv", "satv")

    For Each typo2 In typos
        Selection.Replace What:=typo, Replacement:="sarv", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Next typo
    
Dim typos3 As Variant
typos = Array("marsan", "marchan", "marshal", "marshall", "mashan", "mershan")

    For Each typo3 In typos
        Selection.Replace What:=typo, Replacement:="marshan", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Next typo3
    
Dim typos4 As Variant
typos = Array("findu", "facebook")

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

End Sub

This is after the adjustment:

--------------------------------------------------------------------------------------------------- 

Sub ReplaceTypos()

Dim typos1 As Variant
typos1 = Array("pip?", "bibi", "pi pi")

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

Dim typos2 As Variant
typos2 = Array("shark", "sharv", "sartv", "satv")

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

Dim typos3 As Variant
typos3 = Array("marsan", "marchan", "marshal", "marshall", "mashan", "mershan")

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

Dim typos4 As Variant
typos4 = Array("findu", "facebook")

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

End Sub

---------------------------------------------------------------------------------------------------

Please compare them, and note the difference.

I hope that makes sense

[#9]

Dear Haytam,

It worked perfectly. Thank you very much for your help.

I would love to have the time to learn it properly. All I can only do is to record what I usually repeat and try to understand the commands on Vbasic.

I hope to have the time one day to learn it properly.

Anyway your enormous help . Have a nice week!

[#10]

Hello again Haytam,

I have recorded some new commands within the macro where you have already helped me.

Enclose please find it:

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True

The problem is that the original text:

10:40.XX.bbb.ccc
11:25.ddd.eee.fff
11:33.ggg.hhh.iii
11:42.jjj.kkk.lll
12:10.ppp.qqq

after running the macro, sometimes changes the first text into a time form and others not.

For instance:

10:40

11:25:00:AM

11:42

12:10:00:PM

And i would like to keep the text form.

Can you help me again?

[#11]

Hi Juan,

I've tested this code, but ended up with this result:

Please note that all of the times are in Time format, even if the AM/PM isn't displayed, where it's visible in the formula bar!

I think the reason why you got some cells formatted in AM/PM is that these cells have a preset custom format to be formatted that way!

To keep all the results in Text format, where all cells are aligned to the left by default, just change the second argument for each Array function from 1 to 2 as follows:

Before:

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True

After:

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2)), TrailingMinusNumbers:=True

Or you can change the format option to Text during recording the macro as below screenshot:

Regards

[#12]

Hi Haytam, good morning and thank you very much for your clear and quick answer.

I have realized that the problem comes from imported data.

Data is not always perfectly written. For instance, "16:40" some times comes as "16 40" or "1640". 

So, when executing "text to column", cells having having the "16:40" form are considered as a personalysed form. On the other hand cells having ""16 40" or "1640" are considered as a general form. 

If I use your proposed solution and convert everything into text, before changing "16 40" or "1640"  into "16:40" it would be very difficult to correct the wrong cells. Is there anyway to deal  with this problem?

Thank you very much again

[#13]

If so, before you apply the Text to Column process, you have to make the data consistent somehow.

Supposing that these text times always at the beginning of the string, and the third character of the string should always be ( : ), I would suggest this formula:

=IF(MID(A1,3,1)=":",A1,
IF(MID(A1,3,1)=" ",REPLACE(A1,3,1,":"),
IF(AND(MID(A1,3,1)<>" ",MID(A1,3,1)<>":",A1<>""),REPLACE(A1,3,0,":"),"")))

After that, convert the formulas to values, and then apply the text to columns process.

Hope that helps

[#14]

Thank you again. it works!

However, would it be possible to run those instructions from a macro command?

[#15]

Yes, that possible!

But you cannot record them, you have to craft them as follows:

Sub TextManipulationProcess()
        
On Error Resume Next
    
    Dim filledCells As Integer
    filledCells = Application.WorksheetFunction.CountA(Selection)
    
    Dim cell As Range
    For Each cell In Selection
    
        If Mid(cell, 3, 1) = ":" Then
            'Do nothing
        ElseIf Mid(cell, 3, 1) = " " Then
            cell = Application.WorksheetFunction.Replace(cell, 3, 1, ":")
        ElseIf Mid(cell, 3, 1) <> " " And Mid(cell, 3, 1) <> ":" And cell <> "" Then
            cell = Application.WorksheetFunction.Replace(cell, 3, 0, ":")
        Else
            cell = ""
        End If
        
        Dim counter As Integer
        counter = counter + 1
        
        If counter >= filledCells Then
            Exit For
        End If
    
    Next cell

    Dim r As String
    r = Selection.Range("A1").Address(False, False)
    If Range(r).Value = "" Then Exit Sub
    
    Selection.TextToColumns Destination:=Range(r), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=".", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
    2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2)), TrailingMinusNumbers:=True

On Error GoTo 0

End Sub

Please note that you have to select the column before you run this code.

Hope that helps

[#16]

Good morning Haytam and thank you very much as it worked again!

Do you know  why when I try to delete in a column blank spaces Excel does not find any blank spaces? The column has text but it is under the general format. Even if I change the format to text cells, it does not work...

Columns("C:C").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Thank you very much in advance, Juan

[#17]

Hi Juan,

There is another type of spaces called non-breaking space, and you need to a special trick to get rid of it.

I think this space is what you have in your worksheet.

You can represent this space using this function:

Chr(160)

And this one for the regular space:

Chr(32)

To remove this space in addition to the regular space, please try this code:

Sub RemoveAllSpacesTypes()

    Columns("C:C").Select
    
    Dim spaces As Variant
    spaces = Array(Chr(32), Chr(160))
    
    For Each s In spaces
    
        Selection.Replace What:=s, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Next s

End Sub

Please note that this code will remove all spaces including the spaces in between words.

Regards

[#18]

It worked perfectly. Thank you very much and congratulations!!