提问人:PipS 提问时间:11/5/2023 最后编辑:JohnMPipS 更新时间:11/6/2023 访问量:49
VBA Excel 查找和替换字符限制
VBA Excel find and replace character limit
问:
我正在尝试在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,类型不匹配。
答:
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
评论
Replace
FormulaVersion