查找并替换范围中所有出现的字符串 [英] Find and replace all occurences of a string in a range

问题描述

我想基本上模拟VBA中Excel中的全部替换功能，并将字符串03/01/2018(在此工作簿中存在10次)替换为01/03/2017，我已经弄清楚了如何做到这一点一次出现，但并非一次出现在范围内的所有出现.

I want to basically simulate the replace all feature in Excel inside VBA and replace the string 03/01/2018 (which exists 10 times in this workbook) with 01/03/2017 I already figured out how to do that for one single occurrence but not to all occurrences inside a range.

是否有类似findnext的替换方法?

Is there something like findnext for the replace method?

Sub findandreplacedate()
    Workbooks("01 .xlsx").Sheets(1).usedrange.Replace What:="*03/01/2018*", _
    Replacement:="01/03/2017", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
end sub

推荐答案

您可以尝试一下.这使用RegEx (正则表达式)来检查您的日期.

You can give this a try. This uses RegEx (Regular Expressions) to check for your date.

您将需要设置对 Microsoft VBScript正则表达式x.x

Sub ChangeDates()

    Dim RegEx As New RegExp, rng As Range, i As Long, s As String
    Dim tempArr() As String, bFlag As Boolean

    With RegEx
        .Pattern = "(\d{2})/(\d{2})/(\d{4})"
        For Each rng In ActiveSheet.UsedRange
            tempArr = Split(rng.Text)
            bFlag = False
            For i = 0 To UBound(tempArr)
                If .test(tempArr(i)) Then
                    s = tempArr(i)

                    'Subtract 1 year from original date
                    s = Format(DateAdd("YYYY", -1, CDate(s)), "MM/DD/YYYY")

                    'Swap month and day field
                    tempArr(i) = Format(DateSerial(.Replace(s, "$3"), _
                            .Replace(s, "$2"), .Replace(s, "$1")), "mm/dd/yyyy")

                    'Tell VBA that the string has change and to update sheet
                    bFlag = True
                End If
            Next
            If bFlag = True Then rng.Value = Join(tempArr)
        Next rng
    End With

End Sub  

此表达式分为三组:(\d{2})，(\d{2})，(\d{4})

This expression is divided into three groups: (\d{2}), (\d{2}), (\d{4})

组1和2找到任意两个({2})数字(\d)，后跟一个正斜杠/

Group 1 and 2 finds any two ({2}) digits (\d), followed by a forward slash /

第3组正在寻找正斜杠/

Group 3 is looking for any four ({4}) digits (\d) that comes after a forward slash /

这篇关于查找并替换范围中所有出现的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！