Excel 2010 VBA。连接工作表中的2列并将其粘贴到另一个 [英] Excel 2010 VBA. Concatenate 2 columns from a Sheet and paste them in another

问题描述

使用Excel 2010，我正在尝试创建一个脚本，该脚本将Sheet1中的两个文本列（A和B）连接起来并将结果粘贴到Sheet2的A列中。

Using Excel 2010, I'm trying to create a script that concatenates two text columns (A and B) from Sheet1 and pastes the result in column A of Sheet2.

该工作簿使用外部数据源来加载两个列，因此行数不是固定的。

The workbook uses an external datasource for loading both columns, so the number of rows is not fixed.

我尝试了以下代码，但是没有用。变量lRow没有任何值。

I've tried the following code, but not working. variable lRow is not taking any value.

Sub Concat()
Sheets("Sheet1").Select
Dim lRow As Long
lRow = Range("A" & Rows.count).End(xlUp).Row
For i = 2 To lRow
    ActiveWorkbook.Sheets("Sheet2").Cells(i, 1) = Cells(i, 1) & Cells(i, 2)
Next i

End Sub

我在做什么错。感谢您的帮助！

What am I doing wrong. Thanks for helping!

推荐答案

关于您在做什么错，我建议您使用

As to what are you doing wrong, I suggest you use

Sub Concat()
    Sheets("Sheet1").Select
    Dim lRow As Long, i As Long
    Dim rng As Range
    Set rng = Range("A" & Rows.Count).End(xlUp)
    Debug.Print rng.Address(External:=True)
    lRow = rng.Row
    For i = 2 To lRow
        ActiveWorkbook.Sheets("Sheet2").Cells(i, 1) = Cells(i, 1) & Cells(i, 2)
    Next i
End Sub

正在进行。我完全尝试了您使用的工具，并且对我有用（Excel 2010）。

to see what is going on. I tried exactly what you used and it worked for me (Excel 2010).

指定变量lRow不取任何值的含义将有所帮助。

Specifying what does "variable lRow is not taking any value" mean would help.

您也可以尝试或者

Sub Concat2()
    Sheets("Sheet1").Select
    Dim lRow As Long, i As Long
    Dim rng As Range
    Set rng = Range("A2").End(xlDown)
    Debug.Print rng.Address(External:=True)
    lRow = rng.Row
    For i = 2 To lRow
        ActiveWorkbook.Sheets("Sheet2").Cells(i, 1) = Cells(i, 1) & Cells(i, 2)
    Next i
End Sub

如果您在源列A的中间没有空白单元格，则结果相同。

which should give the same result if yo do not have blank cells in the middle of the source column A.

这篇关于Excel 2010 VBA。连接工作表中的2列并将其粘贴到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！