如何有条件地将一行 Excel 数据从一张工作表附加到另一张工作表? [英] How do I conditionally append a row of Excel data from one sheet to another?

问题描述

我不经常使用 Excel，但我希望有一种相当简单的方法来解决这个问题.我查看了许多其他解决方案，涉及将数据从一张纸粘贴到另一张纸，但我找不到任何可以让我(1)将一个单元格从一张纸匹配到另一张然后(2)有条件地附加或连接数据的解决方案而不是简单地粘贴它.

I don't use Excel very often, but I'm hoping there is a fairly straightforward way to get through this. I looked through a number of other solutions involving pasting data from one sheet to another, but I couldn't find anything that would allow me to (1) match a cell from one sheet to another and then (2) conditionally append or concatenate data instead of simply pasting it over.

我有一个包含两张数据的 Excel 文档.两个工作表都包含一个数字 ID 列.我基本上需要将来自 Sheet2 的 ID 匹配到 Sheet1，然后将来自 Sheet2 的行数据附加到来自 Sheet1 的匹配行.我想它看起来像这样:

I have an Excel document with two sheets of data. Both sheets contain a numerical ID column. I basically need to match the ID's from Sheet2 to the Sheet1 and then append the row data from Sheet2 to the matching rows from Sheet1. I would imagine it will look something like this:

If Sheet2 ColumnA Row1 == Sheet1 ColumnA RowX
  Copy Sheet2 Row1 Columns
  Paste (Append) to Sheet1 RowX (without overwriting the existing columns).

对不起，如果有更好的方法来形成这个问题.我已经成功地思考了自己，现在我觉得我的脸上有一种 Nigel Tufnel 的困惑.

Sorry if there is a better way to form this question. I've managed to think myself in circles and now I feel like I have a confused Nigel Tufnel look on my face.

[更新:更新以阐明要复制的单元格.]

[Update: Updated to clarify cells to be copied.]

推荐答案

我认为这就是你想要做的?

I think this is what you are trying to do?

代码未经测试.我相信它应该有效.如果您有任何错误，请告诉我，我们会在那里处理...

The code is untested. I believe it should work. If you get any errors, let me know and we will take it form there...

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1LR As Long, ws2LR As Long
    Dim i As Long, j As Long, LastCol As Long
    Dim ws1Rng As Range, aCell As Range
    Dim SearchString

    Set ws1 = Sheets("Sheet1")
    '~~> Assuming that ID is in Col A
    '~~> Get last row in Col A in Sheet1
    ws1LR = ws1.Range("A" & Rows.Count).End(xlUp).Row
    '~~> Set the Search Range
    Set ws1Rng = ws1.Range("A1:A" & ws1LR)

    Set ws2 = Sheets("Sheet2")
    '~~> Get last row in Col A in Sheet2
    ws2LR = ws2.Range("A" & Rows.Count).End(xlUp).Row

    '~~> Loop through the range in Sheet 2 to match it with the range in Sheet1
    For i = 1 To ws2LR
        SearchString = ws2.Range("A" & i).Value

        '~~> Search for the ID
        Set aCell = ws1Rng.Find(What:=SearchString, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        '~~> If found
        If Not aCell Is Nothing Then
            LastCol = ws2.Cells(i, ws2.Columns.Count).End(xlToLeft).Column

            '~~> Append values
            For j = 2 To LastCol
                ws1.Cells(aCell.Row, j).Value = ws1.Cells(aCell.Row, j).Value & " " & ws2.Cells(i, j).Value
            Next j
        End If
    Next i
End Sub

HTH

席德

这篇关于如何有条件地将一行 Excel 数据从一张工作表附加到另一张工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！