vba 来自不同工作表的单元格的条件副本

Conditional copy of cell from a different sheet

提问by Stephopolis

I want to take a name from a sheet (let's say A2 in a sheet called 'Names') and search for that same name in another worksheet (say A2 in 'Jobs'). After that name is found in the other worksheet I want to copy the value from the cell right next to it (still in 'Jobs' but B2) and return it to a different cell (E2) in the first sheet ('Names'). I ultimately want to loop though all of the values in A1 of 'Names' and fill in the whole sheet.

我想从工作表中取一个名字（假设名为“姓名”的工作表中的 A2）并在另一个工作表中搜索相同的名称（比如“工作”中的 A2）。在另一个工作表中找到该名称后，我想从它旁边的单元格中复制值（仍在“工作”中，但在 B2 中）并将其返回到第一个工作表（“名称”）中的不同单元格（E2） . 我最终想遍历“名称”的 A1 中的所有值并填写整个工作表。

I have gotten this far:

我已经走了这么远：

Sub fixThis()
    Dim i As Long, j As Long, col1 As Long, col2 As Long, lastrow1 As Long, lastrow2 As Long
    Dim sheetOne As String
    Dim sheetTwo As String

    col1 = 5
    col2 = 1
    sheetOne = "Names"
    sheetTwo = "Job"
    lastrow1 = Cells(Rows.Count, col1).End(xlUp).Row
    lastrow2 = Cells(Rows.Count, col2).End(xlUp).Row

    For i = 2 To lastrow1
        For j = 2 To lastrow2

           If sheetOne.Cells(i, col1).Value = sheetTwo.Cells(j, col2).Value Then

                sheetOne.Cells(i, 6).Value = sheetTwo.Cells(j, 2).Value

           End If
        Next
    Next
End Sub

回答by danielpiestrak

It's okay if you store the sheet names as strings. but when you use them you need to use them to reference the sheet object like this: Sheets("Sheetname").Cells().ValueOr you can use a variable like this:

如果您将工作表名称存储为字符串，则可以。但是当您使用它们时，您需要使用它们来引用这样的工作表对象：Sheets("Sheetname").Cells().Value或者您可以使用这样的变量：

Dim strSheet1name as String
strSheet1name = "Sheet1"

Sheets(strSheet1name).Cells().Value

Finally if you really want to you can declare your own sheet object like

最后，如果您真的想要，您可以声明自己的工作表对象，例如

Dim ws as worksheets
ws = Sheets("Sheet1")

ws.Cells.value

To keep all of your above code the same you need to try replacing

要保持以上所有代码相同，您需要尝试替换

       If sheetOne.Cells(i, col1).Value = sheetTwo.Cells(j, col2).Value Then

            sheetOne.Cells(i, 6).Value = sheetTwo.Cells(j, 2).Value

       End If

With

和

       If Sheets(sheetOne).Cells(i, col1).Value = Sheets(sheetTwo).Cells(j, col2).Value Then

            Sheets(sheetOne).Cells(i, 6).Value = Sheets(sheetTwo).Cells(j, 2).Value

       End If

Then finally, if you're working with multiple sheets, you need to ad dmore detail to these lines:

最后，如果您正在处理多张工作表，则需要在这些行中添加更多详细信息：

lastrow1 = Cells(Rows.Count, col1).End(xlUp).Row
lastrow2 = Cells(Rows.Count, col2).End(xlUp).Row

And change them to something like:

并将它们更改为：

lastrow1 = Sheets(SheetOne).Cells(Sheets(SheetOne).Rows.Count, col1).End(xlUp).Row
lastrow2 = Sheets(SheetTwo).Cells(Sheets(SheetTwo).Rows.Count, col2).End(xlUp).Row

The final version would look something like this:

最终版本看起来像这样：

Sub fixThis()
    Dim i As Long, j As Long, col1 As Long, col2 As Long, lastrow1 As Long, lastrow2 As Long
    Dim sheetOne As String
    Dim sheetTwo As String

    col1 = 5
    col2 = 1
    sheetOne = "Names"
    sheetTwo = "Job"
    lastrow1 = Sheets(sheetOne).Cells(Sheets(sheetOne).Rows.Count, col1).End(xlUp).Row
    lastrow2 = Sheets(sheetTwo).Cells(Sheets(sheetTwo).Rows.Count, col2).End(xlUp).Row

    For i = 2 To lastrow1
        For j = 2 To lastrow2
            If Sheets(sheetOne).Cells(i, col1).Value = Sheets(sheetTwo).Cells(j, col2).Value Then
                Sheets(sheetOne).Cells(i, 6).Value = Sheets(sheetTwo).Cells(j, 2).Value
            End If
        Next j
    Next i
End Sub