+ Reply to Thread
Results 1 to 11 of 11

Find and replace characters.

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Find and replace characters.

    I found this, but I need to only change what is in S and T, down to the last row of each...

    
    Sub StripCharacters()
        Dim RE As Object
        Dim rng As Range
        Set RE = CreateObject("VBScript.RegExp")
        With RE
            .Global = True
            ' Place the WANTED characters between [^ and ]
            .Pattern = "[^a-zA-Z0-9]"
            For Each rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
                rng.Value = .Replace(rng.Value, "")
            Next rng
        End With
    End Sub
    The other issue is I need to make this work on a specific sheet, not the entire workbook...

    What I'd like it to do is to replace the extra characters with a "-" even the ", so that it can be made into a folder. Basically I have this as a script so far:

    With wsJL
        Dim baseFolder As String, newFolder As String
        Dim cell As Range
    
        lastrow = wsJL.Cells(Rows.Count, "B").End(xlUp).Row
    
        baseFolder = CleanName(Sheets("Lists").Range("$G$2").Value)      'folders will be created within this folder
    
        If Right(baseFolder, 1) <> Application.PathSeparator Then baseFolder = baseFolder & Application.PathSeparator
    
        For Each cell In Range("S3:S" & lastrow)    'CHANGE TO SUIT
    
            'Company folder - column A
    
            newFolder = baseFolder & CleanName(cell.Value)
            If Len(Dir(newFolder, vbDirectory)) = 0 Then MkDir newFolder
    
            'Part number subfolder - column C
    
            newFolder = newFolder & Application.PathSeparator & CleanName(cell.Offset(0, 1).Value)
            If Len(Dir(newFolder, vbDirectory)) = 0 Then MkDir newFolder
    
        Next
       End With
    End Sub
    
    Function CleanName(strName As String) As String
    'will clean part # name so it can be made into valid folder name
    'may need to add more lines to get rid of other characters
    
        CleanName = Replace(strName, "/", "")
        CleanName = Replace(CleanName, "*", "")
        CleanName = Replace(CleanName, ".", "")
        CleanName = Replace(CleanName, ",", "")
        CleanName = Replace(CleanName, """", "")
    End Function
    But for some reason it's not replacing the " and the , as it should in theory...
    Last edited by Cyberpawz; 06-04-2012 at 01:16 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1