使用 VBA 向单元格添加单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11476326/
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
Adding single quotes to a cell using VBA
提问by Kiranshell
I am trying to add single quotes to values in a column by using Chr(39), but I am only getting the second quote. e.g. I want 'value'but I am getting value'
我正在尝试使用 Chr(39) 为列中的值添加单引号，但我只得到第二个引号。例如，我想要“价值”，但我正在获得价值“
But if I use double quotes by using Chr(34) it works, and I get "value"
但是如果我通过使用 Chr(34) 使用双引号，它会起作用，并且我得到“值”
Sub AddQuote()
Dim myCell As Range
For Each myCell In Selection
If myCell.Value <> "" Then
myCell.Value = Chr(39) & myCell.Value & Chr(39)
End If
Next myCell
End Sub
Thanks, Kiran
谢谢，基兰
采纳答案by whytheq
just add another single quote:
只需添加另一个单引号：
Sub AddQuote()
Dim myCell As Range
For Each myCell In Selection
If myCell.Value <> "" Then
myCell.Value = Chr(39) & Chr(39) & myCell.Value & Chr(39)
End If
Next myCell
End Sub
回答by JimmyPena
I prefer this syntax. Instead of looping through each cell, just read the whole selection into an array, act on the array, then dump the array contents back onto the worksheet. A maximum of two touches of the worksheet.
我更喜欢这种语法。而不是循环遍历每个单元格，只需将整个选择读入一个数组，对数组进行操作，然后将数组内容转储回工作表。最多两次触摸工作表。
Sub AddQuote()
Dim i As Long, j As Long
Dim inputData As Variant
Dim outputData As Variant
' only act on a range
If TypeName(Selection) = "Range" Then
' assign selection value to a Variant array
inputData = Selection.Value
' create an output array of the same size
ReDim outputData(LBound(inputData) To UBound(inputData), _
LBound(inputData, 2) To UBound(inputData, 2))
' loop through all array dimensions
For i = LBound(inputData) To UBound(inputData)
For j = LBound(inputData, 2) To UBound(inputData, 2)
' array element will be = Empty if cell was empty
If Not IsEmpty(inputData(i, j)) Then
outputData(i, j) = Chr(39) & Chr(39) & inputData(i, j) & Chr(39)
End If
Next j
Next i
Selection.Value = outputData
End If
End Sub
It also accounts for a multiple-column selection.
它还考虑了多列选择。
回答by BonyT
The first quote ' means the cell is treated as text.
第一个引号 ' 表示单元格被视为文本。
So you'll have to use two single quotes on the LHS, and one on the right for it to appear correctly.
因此，您必须在 LHS 上使用两个单引号，并在右侧使用一个单引号才能正确显示。
note if A1= ''test'and A2= ''abc'
注意如果A1=''test'和A2=''abc'
Then =A1&A2will give you 'test''abc'as you'd expect
然后=A1&A2会给你'test''abc'你所期望的
