vba 使用循环将所有文本合并到一列

Using a loop to combine all text down a column

提问by blahblahblah

Looking for help regarding writing a loop that will combine all the text values in a column while adding "OR" in between each one. To give some context, I have filenames stored in a column and I want to write a macro that will combine all those filenames separated by "OR" so I can copy and paste the string into a windows search bar and find all those files in a folder.

寻求有关编写循环的帮助，该循环将组合列中的所有文本值，同时在每个值之间添加“OR”。为了提供一些上下文，我将文件名存储在列中，并且我想编写一个宏来组合所有由“或”分隔的文件名，以便我可以将字符串复制并粘贴到 Windows 搜索栏中，并在一个文件夹。

For example (in Column A)

例如（在 A 列中）

Apples
Oranges
Bananas
Pears
Blueberries

In B1, the result should be Apples OR Oranges OR Bananas OR Pears OR Blueberries.

在 B1 中，结果应该是Apples OR Oranges OR Bananas OR Pears OR Blueberries。

回答by chris neilsen

While learning to use Forand Doloops in VBA is an essential skill, you will find that looping over ranges of cellsis slow, and often too slow to be useful. There is often an alternative, and in this case it's the Joinfunction:

虽然学习在 VBA 中使用For和Do循环是一项基本技能，但您会发现循环单元格范围很慢，而且通常太慢而无用。通常有另一种选择，在这种情况下，它是Join函数：

Function MergeColumn(rng As Range, Delimiter As String) As Variant
    MergeColumn = Join(Application.Transpose(rng.Columns(1).Value), Delimiter)
End Function

How this works:

这是如何工作的：

• It's a UDF, so it can be called from other VBA code, or from a worksheet cell
• You pass it a Rangeobject. It processes only the left most column of that Range
• rng.Columns(1).Valuereturns the left most column, as a 2D VariantArray, size n x 1where nis the number of rows in rng. That is, its dimension is 1 to n, 1 to 1
• Application.Transposetransposes the array. It has the added feature that when passed a n?x?1array it returns a 1D array length n. That is, its dimension is 1?to?n
• Joinconcatenates each member of the array, inserting Delimiterbetween each element.

• 它是一个 UDF，因此可以从其他 VBA 代码或工作表单元格中调用它
• 你传递给它一个Range对象。它只处理该范围的最左边的列
• rng.Columns(1).Value以二维Variant数组形式返回最左边的列，n x 1其中n是 中的行数rng。也就是说，它的维数是1 to n, 1 to 1
• Application.Transpose转置数组。它有一个附加功能，当传递一个n?x?1数组时，它返回一个一维数组长度n。也就是说，它的维数是1?to?n
• Join连接数组的每个成员，Delimiter在每个元素之间插入。

Use it like this

像这样使用它

In VBA

在 VBA 中

Sub Demo
    Dim r as Range
    Dim strResult as String
    ' Get a reference to the range to be processed, eg
    Set r = Range("A1:A10")
    ' Call the function
    strResult = MergeColumn(r, " OR ")
    ' Print Result to Imedieate window
    Debug.Print strResult
End Sub

As a cell formula

作为单元格公式

=MergeText(A1:A10," OR ")

回答by pnuts

With a formula (assuming Apples is in A2 and B1 is blank):

使用公式（假设 Apples 在 A2 中，B1 为空）：

=IF(ISBLANK(B1),A2,B1&" OR "&A2)  

copied down to suit (and output in last row).

向下复制以适应（并在最后一行输出）。

回答by L42

here is a simple example using For Each Loop

这是一个简单的例子，使用 For Each Loop

Dim cel as Range, rng as Range

Set rng = Range("A1","A5")

For Each cel in rng
    With Range("B1")
        If .Value = "" Then
            .Value = cel
        Else
            .Value = .Value & "OR" & cel
        End If
    End With
Next

Using simple For Loop:

使用简单For Loop：

Dim rng as Range, i as integer

Set rng = Range("A1", "A5")

For i = 1 to rng.Rows.Count
    With Range("B1")
        If .Value = "" Then
            .Value = rng.Range("A" & i)
        Else
            .Value = .Value & "OR" & rng.Range("A" & i)
        End If
    End With
Next

Using Do Loop

使用 Do Loop

Dim rng as Range, i as integer

Set rng = Range("A1", "A5")
i = 1

Do Until i > rng.Rows.Count
    With Range("B1")
        If .Value = "" Then
            .Value = rng.Range("A" & i)
        Else
            .Value = .Value & "OR" & rng.Range("A" & i)
        End If
    End With
    i = i + 1
Loop

Hope this helps.

希望这可以帮助。

回答by user3015739

A simple solution. Feel free to modify the code. I did not include much flexibility such as count last row. Just a basic template to show you how to use for loop.

一个简单的解决方案。随意修改代码。我没有包括太多的灵活性，例如计算最后一行。只是一个基本模板，向您展示如何使用 for 循环。

for li_row = 1 to 10
   if str_file = '' then
      str_file = cells(li_row,1).value
   else
      str_file = str_file + ' OR ' + cells(li_row,1).value
   end if
next

cells(1,2).value = str_file

回答by Sathish Kothandam

One more method ..it works upto last used row in column A

另一种方法..它适用于 A 列中最后使用的行

Tested

已测试

Sub testing()
Dim lrow As Integer
Dim erange As Range
Dim str As String
With ActiveSheet
lrow = .Range("A" & Rows.Count).End(xlUp).Row
str = ""
For Each erange In .Range("A2:A" & lrow)

If str = "" Then
str = erange.Value
Else
str = str & " OR " & erange.Value

End If

Next erange

MsgBox (str)


End With

End Sub