vba 复制多个范围并在 Excel 中连接它们
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8589942/
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
Copying multiple ranges and concatenating them in Excel
提问by kinkajou
My Excel sheet has multiple used ranges. I want to copy each range value and concatenate them. What I did is
我的 Excel 工作表有多个使用范围。我想复制每个范围值并将它们连接起来。我所做的是
Set tempRange = Union(SrcWkb.Worksheets("mysheet").Range("F1:H1"), SrcWkb.Worksheets("mysheet").Range("I1:J1"), SrcWkb.Worksheets("NWP").Range("K1:L1"))
For Each eachRange In tempRange
tempString = tempString & eachRange & "/"
MsgBox tempString
Next eachRange
I want to copy the value in merged cells F1:H1 and concatenate a "/" and value from I1:J1 (also merged) and K1 to L1. However, Excel throws "subscript out of range" error. How could I achieve this?
我想复制合并单元格 F1:H1 中的值，并将 I1:J1（也合并）和 K1 中的“/”和值连接到 L1。但是，Excel 会抛出“下标超出范围”错误。我怎么能做到这一点？
回答by Alex P
It is not quite clear from your original post what output you need. Here is one option which may help you get started:
从您的原始帖子中不太清楚您需要什么输出。这是一个可以帮助您入门的选项：
Sub ConcatRanges()
Dim rangeOne As Range, rangeTwo As Range, rangeAll As Range, cl As Range, str As String
Set rangeOne = Worksheets("mysheet").Range("I27:K27")
Set rangeTwo = Worksheets("mysheet").Range("L27:N27")
Set rangeAll = Union(rangeOne, rangeTwo)
For Each cl In rangeAll
str = str & cl & " / "
Next cl
Debug.Print str //Output: 1 / 2 / 3 / 4 / 5 / 6 /
End Sub
Updated Post
更新帖子
Dealing with merged ranges can be tricky. For example, the merged range F1:H1has value 36M. To access the value you have to refer to the first cell in the merged range. Example:
处理合并范围可能很棘手。例如，合并范围的F1:H1值为 36M。要访问该值，您必须引用合并范围中的第一个单元格。例子：
Sub MergedRangeDemo()
Dim rng As Range, cl As Range
Set rng = ActiveSheet.Range("F1:H1")
For Each cl In rng
Debug.Print cl.Value, cl.Address
Next cl
//Output: 36M $F <-- Only first cell contains the value
// $G
// $H
End Sub
Given this you can concatenate the values by using the rowindex (1)of the range:
鉴于此，您可以使用(1)范围的 rowindex 连接值：
Sub ConcatRangesUpdated()
Dim rangeOne As Range, rangeTwo As Range, rangeThree As Range, str As String
Set rangeOne = ActiveSheet.Range("F1:H1")
Set rangeTwo = ActiveSheet.Range("I1:J1")
Set rangeThree = ActiveSheet.Range("K1:L1")
str = rangeOne(1) & " / " & rangeTwo(1) & " / " & rangeThree(1)
Debug.Print str 'Output: 36M / 40M / 36M
End Sub
回答by Rachel Hettinger
It appears that you want to concatenate I27 and L27 with a forward slash between and put the results on a different worksheet. This example does just that: concatenates I27 & L27, J27 & M27, K27 & N27 and puts the results in cells A27:C27 on the destination sheet. Note that the formula uses R1C1 notation with relative column positions; adjust as necessary.
看来您想用正斜杠连接 I27 和 L27，并将结果放在不同的工作表上。这个例子就是这样做的：连接 I27 & L27、J27 & M27、K27 & N27 并将结果放在目标工作表上的单元格 A27:C27 中。请注意，该公式使用带有相对列位置的 R1C1 表示法；根据需要进行调整。
Sub ConcatCells()
Dim sSource As String
sSource = "'" & SrcWkb.Worksheets("mysheet").Name & "'!"
DstWks1.Range("A27:C27").FormulaR1C1 = "=" & sSource & "RC[8] & " _
& Chr$(34) & "/" & Chr$(34) & " & " & sSource & "RC[11]"
End Sub
