Find and Replace

[whitoulias]

Hi all,

I have created a macro which reads column B and then creates separate sheets based on the values-text found on that Column.

I obviously get an error while new sheets are created. The 1st one is when text is over 31 characters. This is resolved.

The 2nd one is when i have the 7 sin characters (/, \, *, [, ], ? , :, ) and sheet name cannot be created.

Ideally i would like to have the below code like 7 times, each for every character, but this doesn't work.

Range("B2:B" & lastRow(ActiveSheet)).Select
Selection.Replace What:="/", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Any thought would be appreciated.

Thank you

 

[Domenic]

Try...

    Dim sinChars As Variant
    sinChars = Array("/", "\", "~*", "[", "]", "~?", ":")
    
    Dim i As Long
    With Range("B2:B" & lastRow(ActiveSheet))
        For i = LBound(sinChars) To UBound(sinChars)
            .Replace what:=sinChars(i), replacement:="_", Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
        Next i
    End With

Note that since the asterisk (*) and question mark (?) are wildcards, the escape character tilde (~) is used in order to refer to the actual character.

Hope this helps!

 

[whitoulias]

You were right about the asterisk (*) and question mark (?). That is why my code did not work in the 1st place. I should have thought.
Your code works great.
Thank you for your time!!!

 

[Domenic]

You're very welcome, cheers!