vba 将多个单元格合并为一个的宏，值用逗号分隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18494452/
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
Macro combining multiple cells into one, with values separated by a comma
提问by Wendy McEnroe Griffin
In a previous posted answer, Combine multiple cells into one in excel with macro?, the following macro was provided..which works great by the way!
在之前发布的答案中，使用宏将多个单元格合并为一个在 excel 中的单元格？，提供了以下宏..顺便说一句，效果很好！
However, I need to separate the cell values with a comma. I have tried inserting a comma between the quotes "," but this is not working. Can someone please guide me so the resulting cell will separate the values with a comma? Thank you!
但是，我需要用逗号分隔单元格值。我试过在引号 "," 之间插入一个逗号，但这不起作用。有人可以指导我，以便生成的单元格用逗号分隔值吗？谢谢！
Sub JoinCells()
Set xJoinRange = Application.InputBox(prompt:="Highlight source cells to merge", Type:=8)
xSource = 0
xSource = xJoinRange.Rows.Count
xType = "rows"
If xSource = 1 Then
xSource = xJoinRange.Columns.Count
xType = "columns"
End If
Set xDestination = Application.InputBox(prompt:="Highlight destination cell", Type:=8)
If xType = "rows" Then
temp = xJoinRange.Rows(1).Value
For i = 2 To xSource
temp = temp & " " & xJoinRange.Rows(i).Value
Next i
Else
temp = xJoinRange.Columns(1).Value
For i = 2 To xSource
temp = temp & " " & xJoinRange.Columns(i).Value
Next i
End If
xDestination.Value = temp
End Sub
回答by tigeravatar
Have a look here: http://www.excelforum.com/tips-and-tutorials/860240-concatall-udf-by-tigeravatar.html
看看这里：http: //www.excelforum.com/tips-and-tutorials/860240-concatall-udf-by-tigeravatar.html
The first post shows how to use it, and the 8th post is the most recent version.
第一篇文章展示了如何使用它，第 8 篇文章是最新版本。
[EDIT]
Alternately, just change both instances of & " " &to be & "," &
[编辑] 或者，只需将两个实例更改& " " &为& "," &
回答by Declan_K
Here is the function I use to do this. It only works for a single column, but can be easily adapted to work for multiple columns if necessary.
这是我用来执行此操作的函数。它仅适用于单列，但如果需要，可以轻松适应多列。
Function CommaList(DataRange As Range, Optional Seperator As String = ",", Optional Quotes As Boolean = True) As String
Dim iCellNum As Integer
Dim sTemp As String
If DataRange.Columns.Count > 1 Then
CommaList = "Select a Single Column"
Exit Function
End If
For iCellNum = 1 To DataRange.Cells.Count
If Quotes Then
sTemp = sTemp + "'" + DataRange.Cells(iCellNum, 1) + "'"
Else
sTemp = sTemp + DataRange.Cells(iCellNum, 1)
End If
If iCellNum < DataRange.Cells.Count Then
sTemp = sTemp + Seperator
End If
Next iCellNum
CommaList = sTemp
End Function
