将单元格数据从一张纸复制到另一张纸，具体取决于两张纸上的匹配列值 [英] copy cell data from one sheet to another depending on matching column values in both sheets

问题描述

我在论坛中看到了类似的问题(几乎相同的问题).这是选择的答案.

I saw the similar (almost same question) in the forum. Here is the chosen answer.

代码:

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

但是我不知道如何将其转化为我的案子.

But I dont know how to turn it into answer for my case.

我有2张纸，名字分别是add和remove.我想比较两张纸的第一列.如果两个工作表中的值都匹配，我想将状态列的值从工作表添加"复制到工作表删除"以获取匹配的值.

I have 2 sheets with name add and remove. I want to compare first column of both sheets. If a value matches in both sheets, I want to copy the value of status column from sheet "add" to sheet "remove" for the matched value.

请帮助我.

Ps:我是编码的初学者.

Ps: I am very beginner at coding.

推荐答案

Sub fixThis()
    Dim i As Long, j As Long, colStatus As Long, lastrowAdd As Long, lastrowRemove As Long

    colStatus = 2 'your status column number
    lastrowAdd = Sheets("Add").Cells(Sheets("Add").Rows.Count, 1).End(xlUp).Row
    lastrowRemove = Sheets("Remove").Cells(Sheets("Remove").Rows.Count, 1).End(xlUp).Row

    For i = 1 To lastrowAdd 
        For j = 1 To lastrowRemove 
            If Sheets("Add").Cells(i, 1).Value = Sheets("Remove").Cells(j, 1).Value Then
                Sheets("Remove").Cells(j, colStatus).Value = Sheets("Add").Cells(i, colStatus).Value
            End If
        Next j
    Next i
End Sub

这篇关于将单元格数据从一张纸复制到另一张纸，具体取决于两张纸上的匹配列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！