在 Excel VBscript / VBA 中插入行返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42859160/
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
Inserting a line return in Excel VBscript / VBA
提问by Claus
Using Excel v16
使用 Excel v16
I'm trying to format text in a cell by replacing a character like tilde ~ with a carriage return whereby the finished result would be multiple lines. This is actually a merged cell of multiple rows and columns as shown below
我试图通过用回车替换像波浪号 ~ 这样的字符来格式化单元格中的文本，这样最终结果将是多行。这实际上是多行多列的合并单元格，如下图
This is the input
这是输入
This is the desired output
这是所需的输出
Here is the script I'm using in my macro
这是我在宏中使用的脚本
Sub FindReplaceAll()
Sheets("Template").Select
Range("A34").Select
iStr = ActiveCell.Value
For i = 1 To Len(iStr)
If Mid(iStr, i, 1) = "~" Then
rtStr = rtStr + vbCr
Else
rtStr = rtStr + Mid(iStr, i, 1)
End If
Next i
ActiveCell.Value = rtStr
End Sub
Unfortunately all I get is same line with the tilde removed The cell is formatted with the wrap text. Not sure where to go from here.
不幸的是，我得到的只是删除了波浪号的同一行单元格使用换行文本进行格式化。不知道从这里去哪里。
回答by JNevill
The entirety of your subroutine can be replaced with just a single line:
整个子程序可以只用一行替换：
sub findReplaceAll()
Sheets("template").Range("A34").Value = Replace(Sheets("template").Range("A34").value, "~", chr(13))
end sub
That will set that cell's value to itself, but replacing the tildes with a carriage return. As mentioned in @johncoleman's answer you can use vbcrlffor a carriage return/line feed or chr(13) & chr(10)if you want to split hairs.
这会将单元格的值设置为自身，但用回车符替换波浪号。正如@johncoleman 的回答中提到的，您可以将其vbcrlf用于回车/换行，或者chr(13) & chr(10)如果您想分开头发。
This could also be done in a worksheet formula with:
这也可以在工作表公式中完成：
=SUBSTITUTE(A34, "~", CHAR(13))
回答by John Coleman
In general, in Windows for newlinesyou would need vbCrLFrather than vbCr. Multiline strings can be written to an Excel cell using either vbCrLfor simply vbLf. Doing the later corresponds directly with what would happen if you enter such a string from the user interface using Alt+Enter.
一般来说，在 Windows 中换行你需要vbCrLF而不是vbCr. 可以使用vbCrLf或简单地将多行字符串写入 Excel 单元格vbLf。如果您从用户界面使用Alt+Enter.
Doing this potentially causes problems if you intend to do something else with the string other than display it in a cell, such as write it to a text file or copy it to the clipboard and paste it to a different application. For example, if in cell A1you type a Alt+Enter byou will see adisplayed directly over b, but if you run the following code:
如果您打算对字符串执行其他操作而不是在单元格中显示它，则这样做可能会导致问题，例如将其写入文本文件或将其复制到剪贴板并将其粘贴到不同的应用程序。例如，如果A1您在单元格中键入，a Alt+Enter b您将看到a直接显示在 上b，但如果您运行以下代码：
Sub test()
Dim FSO As Object
Dim f As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set f = FSO.CreateTextFile("C:/programs/test.txt")
f.Write Range("A1").Value
f.Close
End Sub
and then inspect the file in Notepad, the line breaks won't display. For these sorts of reasons, I almost always use vbCrLfwhen building up multi-line strings in VBA.
然后在记事本中检查文件，不会显示换行符。由于这些原因，我几乎总是vbCrLf在 VBA 中构建多行字符串时使用。
Thus -- if you want to simply display the string use either chr(10)or vbLf, but it you might want to later use this string somewhere else, use vbCrLf.
因此 - 如果您只想显示字符串，请使用chr(10)或vbLf，但您可能希望稍后在其他地方使用此字符串，请使用vbCrLf。
