如何遍历行和列并在 Excel VBA 中连接两个文本单元格？

How to loop through rows and columns and Concatenate two text cells in Excel VBA?

提问by Clayton Borgmeyer

I am fairly new to Excel Macros and I am looking for a way to loop through the row headings and columns headings and combine them into one cell for each row and column heading until I have combined all of them.

我对 Excel 宏还是很陌生，我正在寻找一种方法来循环遍历行标题和列标题，并将它们合并到每个行和列标题的一个单元格中，直到我将它们全部合并。

An example of the First Column cell would be "Your Organizations Title"

第一列单元格的示例是“您的组织标题”

An Example of the First Row Cell Would be "22. Cheif Investment Officer"

第一行单元格的一个例子是“22.首席投资官”

An example of the first combined cell that I want on a new sheet would be this: "22. Chief Investment Officer (Your Organization's Title)

我想要在新工作表上的第一个组合单元格的示例是：“22. 首席投资官（贵组织的职务）

I then want the combined cells on the new sheet to offset one column to the right until it has iterated through all of the rows and columns.

然后我希望新工作表上的组合单元格向右偏移一列，直到它遍历所有行和列。

I have just joined the forum and it will not let me post images or I would have. Perhaps this gives a better idea, here is my code now:

我刚加入论坛，它不会让我张贴图片，否则我会。也许这给出了一个更好的主意，这是我现在的代码：

Sub Fill()

' Select cell A2, *first line of data*.
Set title = Sheets("Compensation, 3").Range("B6:B500")
Set descr = Sheets("Compensation, 3").Range("C5:AAA5")
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(title.Value)
  Do Until IsEmpty(descr.Value)
    ActiveCell.Offset(0, 1).Formula = _
      "=title.value & "" ("" & descr.value & "")"""
    Set descr = descr.Offset(0, 1)
  Loop
  Set title = title.Offset(1, 0)
Loop

End Sub

When I run it goes puts this into the active cell:
=title.value & " (" & descr.value & ")"
It does not recognize the variables and come up with the NAME error. It also goes into an infinite loop with no output besides the one cell.

当我运行时，它会将其放入活动单元格中：
=title.value & " (" & descr.value & ")"
它无法识别变量并出现 NAME 错误。它也会进入一个无限循环，除了一个单元格之外没有任何输出。

Edit: I cannot answer my own question because I am new to the forum, but using a combination of your answers I have solved the problem! Here is the finished code:

编辑：我无法回答我自己的问题，因为我是论坛的新手，但是结合您的回答我已经解决了问题！这是完成的代码：

Sub Fill()
  ' Select cell A2, *first line of data*.
  Set title = Sheets("Compensation, 3").Range("B6")
  Set descr = Sheets("Compensation, 3").Range("C5")
  offsetCtr = 0
  ' Set Do loop to stop when an empty cell is reached.
  Do Until IsEmpty(title.Value)
      Do Until IsEmpty(descr.Value)
         ActiveCell.Offset(0, offsetCtr).Formula = title.Value & " (" & descr.Value & ")"
         offsetCtr = offsetCtr + 1 
         Set descr = descr.Offset(0, 1)
     Loop
     Set descr = Sheets("Compensation, 3").Range("C5")
     Set title = title.Offset(1, 0)
  Loop

End Sub

Thank you so much!

非常感谢！

回答by shahkalpesh

Option Explicit
Sub GenerateAndPasteFormulaForTitleAndDescription( _
ByVal titlesRange As Range, ByVal descriptionRange As Range, _
ByVal startCellOnDestination As Range)
Dim title As Range
Dim descr As Range

Dim offsetCtr As Long

Dim formulaTemplate As String
Dim newFormula As String

formulaTemplate = "=CONCATENATE([1], '(', [2], ')')"


startCellOnDestination.Worksheet.EnableCalculation = False

For Each title In titlesRange.Cells
    For Each descr In descriptionRange.Cells
        If title.Value <> "" And descr.Value <> "" Then
            newFormula = Replace(formulaTemplate, "[1]", _
                title.Address(External:=True))
            newFormula = Replace(newFormula, "[2]", _
                descr.Address(External:=True))
            newFormula = Replace(newFormula, "'", Chr(34))

            startCellOnDestination.Offset(0, offsetCtr).Formula = newFormula
            offsetCtr = offsetCtr + 1
        End If
    Next
Next

startCellOnDestination.Worksheet.EnableCalculation = True
End Sub

Here is how to call the above procedure

下面是如何调用上述过程

GenerateAndPasteFormulaForTitleAndDescription _
   Sheets("Compensation, 3").Range("B6:B500"), _
   Sheets("Compensation, 3").Range("C5:AAA5"), _
   Sheets("new sheet").Range("B5")

EDIT: The code loops through combination of title and description, checks if both of them aren't empty and creates a formula. It pastes the formula into the start cell (Sheets("new sheet").Range("B5")in this case) and moved ahead and pastes the next formula in the column next to it

编辑：代码循环遍历标题和描述的组合，检查它们是否都为空并创建一个公式。它将公式粘贴到起始单元格（Sheets("new sheet").Range("B5")在这种情况下）并向前移动并将下一个公式粘贴到它旁边的列中

回答by PowerUser

Basically, you are trying to use VBA objects in worksheet functions. It doesn't quite work that way.

基本上，您正在尝试在工作表函数中使用 VBA 对象。它不是这样工作的。

Try replacing

尝试更换

"=title.value & "" ("" & descr.value & "")"""

"=title.value & "" ("" & descr.value & "")"""

with

和

=title.value & " (" & descr.value & ")"

=title.value & " (" & descr.value & ")"