VBA Excel 查找和替换字符限制

VBA Excel find and replace character limit

提问人:PipS 提问时间:11/5/2023 最后编辑:JohnMPipS 更新时间:11/6/2023 访问量:49

问:

我正在尝试在Excel中用长句替换小句子。 我知道它的工作原理是,当我尝试只替换几个字符时,它就会这样做。但它不喜欢长句。我可以输入一个长句子,所以我知道单元格可以处理字符的数量。 有硬性字符限制吗?如果是这样,可以改变吗?如果有帮助,我只想在 U 列中搜索。

这是我的代码(请原谅第一部分,因为我录制了一个宏,它保留了我的单元格选择):

Sub FindReplaceWords()
    '
    ' FindReplaceWords Macro
    '

    '
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "That they were born this year - First Christmas!"
    Range("M11").Select

    Cells.Replace What:="That they were born this year - First Christmas!", _
        Replacement:="My elves are hard at work making sure we have something extra-special waiting for you under the tree this year - although nothing can compare to receiving such a precious gift as yourself. You'll be able to enjoy so many wonderful things as time passes: learning how to crawl and talk, discovering new places and people; but being part of a loving family will always be one of life's greatest treasures.  On behalf of myself and all my elves here at The North Pole we wish all good tidings during this festive season, hope lots of joy abounds throughout your home and many happy memories await both today & tomorrow!", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
        False, FormulaVersion:=xlReplaceFormula2

    Cells.Replace What:="That they started nursery this year", Replacement:= _
        "I just wanted to take a moment and congratulate you on starting nursery! How exciting it must be for you to learn new things and make friends this year. I know that it can be a bit daunting starting something new, but don't worry – everyone is so kind at the nursery and they will look after you until pick-up time. You're going to have such an amazing time there.”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        FormulaVersion:=xlReplaceFormula2 

    Cells.Replace What:="That they started preschool this year", Replacement:= _
        "I wanted to take this opportunity to congratulate you on such a big milestone. Starting preschool can be a little scary but it also means so much growth and learning ahead for you! I'm sure by now you've been very busy with all the new things that come along with starting school. You must be meeting lots of new friends, playing games and make lots of great art and craft projects", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        FormulaVersion:=xlReplaceFormula2

    Cells.Replace What:="That they started primary school this year", _
        Replacement:="This year has been a special one for you as you started primary school! Primary school is such an important milestone and I’m so proud of you for taking on the challenge. You must have learned so many new things this year; about numbers and letters, about friendship and kindness, about all sorts of interesting things.", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
        :=False, FormulaVersion:=xlReplaceFormula2

End Sub

我收到运行时错误 13,类型不匹配。

Excel VBA 替换 查找

评论

1赞 xShen 11/5/2023
在你的代码中,你有这样的引号“ chr(148) 而不是那个 ” chr(34) (不一样)。只需更换它们
0赞 JohnM 11/5/2023
“类型不匹配”错误的原因是 Range.Replace 方法没有“FormulaVersion”属性...删除它(从每个)并重试。Replace
1赞 taller 11/6/2023
@JohnM Excel 365 有参数,尽管它没有列在联机文档中。FormulaVersion
0赞 JohnM 11/6/2023
很公平......我猜,好老 MS 让他们的文档保持最新!

答:

0赞 John Williams 11/6/2023 #1

有 255 个字符的限制。如果运行此操作,它将在 n = 256 处中断。

Sub FindReplaceLimit()
    Dim n As Long
    
    With ActiveSheet
    For n = 1 To 1000
        .Cells(1, 1) = "x"
       .Cells(1, 1).Replace "x", String(n, "x")
    Next
    End With
End Sub

如果替换 vba 中的字符串而不是使用单元格公式,则长度为单元格的字符限制 32,767。“s”的长度可以大于 32,767,但只有前 32,767 个字符才会复制到单元格中。

Sub FindReplaceLimit2()
    Dim s As String
    
    With ActiveSheet
        .Cells(1, 1) = "x"
        s = .Cells(1, 1)
        s = Replace(s, "x", String(32767, "x"))
       .Cells(1, 1) = s
    End With 
End Sub