使用 Excel VBA 进行字符串替换（类似于 Excel SUBSTITUTE 函数）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8927498/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
string substitution using Excel VBA (similarly to the Excel SUBSTITUTE function)
提问by Laszlo
I have a question regarding string modification. Let's assume that we have the following string: 4155595208***
in cell A1 (10 numbers and 3 starts).
我有一个关于字符串修改的问题。假设我们有以下字符串：
单元格 A1 中的4155595208*** （10 个数字和 3 个开头）。
Now I would like to substitute these 3 stars with a blank field and get the result in cell B1 (so I want 4155595208 to get as a result). If I used a normal built-in Excel function I would type the following thing into cell B1:
=SUBSTITUTE(A1,"*","")
现在我想用一个空白字段替换这 3 颗星，并在单元格 B1 中得到结果（所以我想要 4155595208 作为结果）。如果我使用普通的内置 Excel 函数，我会在单元格 B1 中键入以下内容：
=SUBSTITUTE(A1,"*","")
Now I would like to do the very same thing using Excel VBA. Whenever I tried to run the following code however, I got the error message: "Run-time error '13': Type mismatch".
现在我想使用 Excel VBA 做同样的事情。但是，每当我尝试运行以下代码时，都会收到错误消息：“运行时错误‘13’：类型不匹配”。
sub test()
cells(1,2) = "=SUBSTITUTE(M2," * ","")"
end sub
sub test()
cells(1,2) = "=SUBSTITUTE(M2," * ","")"
end sub
I guess the problem might occur because of the bunch of quotation marks but I don't know. Could someone help me with this matter?
我想问题可能是由于一堆引号引起的，但我不知道。有人可以帮我解决这个问题吗？
Thanks in advance,
Laszlo
提前致谢，
拉兹洛
采纳答案by Michael
You need to use double double quotes when writing formulas
写公式时需要使用双双引号
Cells(1, 2) = "=SUBSTITUTE(M2,"" * "","""")"
