使用vba连接多个范围 [英] Concatenate multiple ranges using vba

问题描述

我希望有人能帮我解决问题。基本上，我有一些范围，我需要独立连接，并将级联范围的值放入不同的单元格。例如我想要：
连接值在范围A1：A10，并将结果放在F1
然后我想连接范围B1：B10并将结果放在F2
然后我想连接C1：C10范围，并将结果放在F3等。

我试图使用以下宏。但我被卡住了宏观似乎正在做的是连接A1：A10，然后将结果放入F1（这是我想要的）。然而，它也将信息从第一个连接存储到内存中，以便当它进行下一个连接时，在单元格F2中，我得到F1和F2的连接结果。

我已经尝试搜索很多论坛，但是由于这是一个我自己找的代码，我找不到一个解决方案，我相信这是一个常见的问题，我做错了，可能没有正确设置变量。

提前感谢您的帮助，

  Sub concatenate（）
 
 Dim x As String 
 Dim Y As String 
 
对于m = 2至5 
 
 Y =工作表（变量）。单元格m，5）.Value 
 
'以上基本上有范围信息，例如a1：表格变量中的a10 
 
对于范围内的每个单元格（&&）范围A1：A10 
如果Cell.Value =然后GoTo Line1'这告诉宏继续，直到空白单元格达到
 x = x& Cell.Value& ，'这提供了连接的单元格值
下一个
 
 Line1：
 
 ActiveCell.Value = x 
 
 ActiveCell.Offset 1，0）。选择
 
下一步m 
 
 End Sub 
  

解决方案

这是我的ConcatenateRange。如果你愿意，它可以添加一个分隔符。它被优化以处理大范围，因为它通过将数据转储到变量数组中并在VBA中处理它来工作。

您将使用它：

  = ConcatenateRange（A1：A10）
  

代码：

 函数ConcatenateRange（ByVal cell_range作为范围，_ 
可选ByVal分隔符As String）As String 
 
 Dim cell As range 
 Dim newString As String 
 Dim cellArray As Variant 
 Dim i As Long，j As Long 
 
 cellArray = cell_range.Value 
 
对于i = 1到UBound（cellArray，1）
对于j = 1到UBound（cellArray，2）
如果Len（cellArray （i，j））< 0然后
 newString = newString& （seperator& cellArray（i，j））
 End If 
 Next 
下一个
 
如果Len（newString）<> 0然后
 newString = Right $（newString，（Len（newString） -  Len（seperator）））
 End If 
 
 ConcatenateRange = newString 
 
结束功能
  

I am hoping someone can help me with my problem. Basically, I have a number of ranges which I need to concatenate independently and put the values of the concatenated ranges into different cells. For example I want to: concatenate values in Range A1:A10 and put the result in F1 then I want to concatenate the Range B1:B10 and put the result in F2 then I want to concatenate the Range C1:C10 and put the result in F3 etc

I have tried to use following macro. However I get stuck; what the macro seems to be doing is concatenating range A1:A10 and then putting the results into F1 (which is what I want). However it also stores the information from the first concatenation into memory so that when it does the next concatenation, in cell F2 I get the concatenated results of F1 and F2 joined.

I have tried searching lots of forums, but since this is a code I made myself I can't find a solution, I am sure this is a common problem and that I am doing something wrong possibly not setting the variable correctly.

Thanks in advance for your help,

Sub concatenate()

    Dim x As String
    Dim Y As String

For m = 2 To 5

    Y = Worksheets("Variables").Cells(m, 5).Value 

'Above essentially has the range information e.g. a1:a10 in sheet variables

For Each Cell In Range("" & Y & "") 'i.e. range A1:A10
    If Cell.Value = "" Then GoTo Line1 'this tells the macro to continue until a blank cell is reached
    x = x & Cell.Value & "," 'this provides the concatenated cell value
Next

Line1:

ActiveCell.Value = x

ActiveCell.Offset(1, 0).Select

Next m

End Sub

解决方案

Here is my ConcatenateRange. It allows you to add a seperator if you please. It is optimized to handle large ranges since it works by dumping the data in a variant array and working with it within VBA.

You would use it like this:

=ConcatenateRange(A1:A10)

The code:

Function ConcatenateRange(ByVal cell_range As range, _
                    Optional ByVal seperator As String) As String

Dim cell As range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString

End Function

这篇关于使用vba连接多个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！