Excel查找和替换字符限制

6l7fqoea 于 2023-11-20 发布在其他

关注(0)|答案(1)|浏览(148)

我试图在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

来源：https://stackoverflow.com/questions/77426249/vba-excel-find-and-replace-character-limit

1条答案

按热度按时间

a11xaf1n1#

有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

型

赞(0）回复(0）举报 2023-11-20

我来回答

Excel查找和替换字符限制

1条答案

相关问题

热门标签

最新问答