Find and Replace

whitoulias

Board Regular
Joined
Jun 22, 2012
Messages
146
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
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
Try...

Code:
    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!
 
Last edited:

whitoulias

Board Regular
Joined
Jun 22, 2012
Messages
146
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!!!
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top