Results 1 to 2 of 2

How to make the multiple regex replace in one loop?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-01-2016
    Location
    China, beijing
    MS-Off Ver
    2016
    Posts
    114

    How to make the multiple regex replace in one loop?

    I want to regex replace the cell in one column 3 times, which A ---> 10, B ---> 11, C ---> 12

    I know how to use the regex for once, but I need to use the code for 3 times in the vba, it seems so tedious. But I haven't found a easier way yet. Any better solution here?

        Set RegExp = CreateObject("vbscript.regexp")
        RegExp.Pattern = "B"
         
        Set SearchRange = ActiveSheet.Range("A4:A100")
        
        For Each Cell In SearchRange
            Set Matches = RegExp.Execute(Cell.Value)
            If Matches.Count >= 1 Then
                Set Match = Matches(0)
                Cell.Value = RegExp.Replace(Cell.Value, "11")
            End If
        Next

    Update tnx a lot for your reply ,
    I now use call sub
    
    Sub Main()
     Call RegExp_Replace("A259", "JustTest", "key")
    End Sub
     
    
    Private Sub RegExp_Replace(OpRange, MatchStr, ReplaceStr)
    
        Dim RegExp As Object
        Dim SearchRange As Range, Cell As Range
        
        '此处定义正则表达式
        Set RegExp = CreateObject("vbscript.regexp")
        RegExp.Pattern = MatchStr
         
        '此处指定查找范围
        Set SearchRange = ActiveSheet.Range(OpRange)
        
        '遍历查找范围内的单元格
        For Each Cell In SearchRange
            Set Matches = RegExp.Execute(Cell.Value)
            If Matches.Count >= 1 Then
                Set Match = Matches(0)
                Cell.Value = RegExp.Replace(Cell.Value, ReplaceStr)
            End If
        Next
    
    End Sub
    Last edited by woshichuanqilz; 04-06-2017 at 12:04 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Looking for a way to find and replace just letters in a regex style manner
    By Wegener in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2013, 02:20 PM
  2. [SOLVED] RegEx replace in range with lagre data set as sub/function?
    By knutfh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2013, 03:33 AM
  3. Creating a macro using the RegEx Find/Replace add-in
    By bauman312 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2012, 12:52 PM
  4. Find & replace loop over multiple columns
    By bigcol77 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2012, 02:24 PM
  5. replace multiple ifs in a do.. loop request
    By nicer2011 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2011, 06:14 PM
  6. Search and replace(Regex) in string in array.
    By VBABen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-22-2011, 09:21 AM
  7. Best Loop to replace multiple strings
    By ker9 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-22-2011, 04:38 PM

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