使用 Excel 中的 VBA 将 2 个单元格的内容合并到另一个第 3 个单元格中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/621862/
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
Merge the contents of 2 cells into another 3rd cell using VBA in Excel
提问by Harry
I have two cells lets say: A1 and A2
我有两个单元格可以说：A1 和 A2
The content of each one of them is a string:
每一个的内容都是一个字符串：
A1: Hallo
A1：你好
A2: World
A2：世界
My goal is to merge the contents of A1 and A2 in another cell e.g. A3 i.e. A3's content should be:
我的目标是将 A1 和 A2 的内容合并到另一个单元格中，例如 A3，即 A3 的内容应该是：
Hallo World
你好世界
I would like to do this using a VBA macro and not only for strings as contents..
我想使用 VBA 宏来做到这一点，而不仅仅是将字符串作为内容..
Thanks both of u for your answers!!
谢谢两位的回答！！
回答by Lunatik
Although, as MasterMix says, this is most easily achieved by a formula, if you have a reason why VBA must be used then it depends on how you wish to specify the cells.
虽然，正如 MasterMix 所说，这最容易通过公式实现，但如果您有必须使用 VBA 的理由，那么这取决于您希望如何指定单元格。
You could do this as a function:
您可以将其作为函数执行：
Private Function addTwoCells(rngA As Range, rngB As Range) As String
addTwoCells = rngA & rngB
End Function
All this does is replicate the (much faster) built-in Excel concatenate function though.
尽管如此，所有这些都是复制（更快）内置的 Excel 连接函数。
You could also do it in one of about a hundred ways in a procedure, here's one way that prompts the user for the ranges:
您也可以在一个过程中以大约一百种方式中的一种方式来完成，这是提示用户输入范围的一种方式：
Private Sub addTwoCellsProc()
Dim rngA As String
Dim rngB As String
Dim rngOutput As String
Dim rngTest As Range
Do
rngA = InputBox("Please enter first cell address", "Cell A")
rngA = Range(rngA).Cells(1, 1).Address
Set rngTest = Intersect(Range(rngA).Cells(1, 1), ActiveSheet.Cells)
Loop Until Not rngTest Is Nothing
Do
rngB = InputBox("Please enter second cell address", "Cell B")
rngB = Range(rngB).Cells(1, 1).Address
Set rngTest = Intersect(Range(rngB), ActiveSheet.Cells)
Loop Until Not rngTest Is Nothing
Do
rngOutput = InputBox("Please enter destination cell address", "Output cell")
Set rngTest = Intersect(Range(rngOutput), ActiveSheet.Cells)
Loop Until Not rngTest Is Nothing
Range(rngOutput) = Range(rngA) & Range(rngB)
End Sub
You could also use predefined ranges and loop through them if you have multiple ranges to combine. If you explain a bit more about the scenario then someone might provide more specific code.
如果您有多个要组合的范围，您还可以使用预定义的范围并循环遍历它们。如果您对场景进行更多解释，那么有人可能会提供更具体的代码。
回答by CodeMonkey1313
I suggest either an Excel formula
我建议使用 Excel 公式
=A1&A2
or a VBA macro
或 VBA 宏
Range("A3").Cell.Value = Range("A1").Cell.Value & Range("A2").Cell.Value
回答by Tim B
This one is quicker, just select the cells and they are merged into the first cell.
这个更快，只需选择单元格并将它们合并到第一个单元格中。
'------------------------------------------------------------------------
' Procedure : Concatenate Text
' Author : Tim Bennett
' Date : 11/6/2015
' Purpose : Concatenate selected text into first column
'------------------------------------------------------------------------
'
'Sub Concatenate_Formula(bConcat As Boolean, bOptions As Boolean)
Sub Concatenate()
Dim rSelected As Range
Dim c As Range
Dim sArgs As String
Dim bCol As Boolean
Dim bRow As Boolean
'Set variables
Set rOutput = ActiveCell
bCol = False
bRow = False
On Error Resume Next
'Use current selection
Set rSelected = Selection
On Error GoTo 0
'Only run if cells were selected and cancel button was not pressed
If Not rSelected Is Nothing Then
sArgs = "" 'Create string of cell values
firstcell = ""
For Each c In rSelected.Cells
If firstcell = "" Then firstcell = c.Address(bRow, bCol)
sArgs = sArgs + c.Text + " " 'build string from cell text values
c.Value = "" ' Clear out the cells taken from
Next
'Put the result in the first cell
Range(firstcell).Value = sArgs
End If
End Sub
回答by JohnyL
In a more general case, here's a macro which concatenates any number of cells (even non-adjacent blocks of cells) Note: I didn't include code which checks user's cancellation.
在更一般的情况下，这是一个连接任意数量的单元格（甚至是不相邻的单元格块）的宏 注意：我没有包含检查用户取消的代码。
Sub G()
Dim strFinal$
Dim cell As Range
Dim rngSource As Range
Dim rngArea As Range
Dim rngTarget As Range
Set rngSource = Application.InputBox("Select cells to merge", Type:=8)
Set rngTarget = Application.InputBox("Select destination cell", Type:=8)
For Each rngArea In rngSource
For Each cell In rngArea
strFinal = strFinal & cell.Value & " "
Next
Next
strFinal = Left$(strFinal, Len(strFinal) - 1)
rngTarget.Value = strFinal
End Sub
