如何使用VBA搜索特定值并将结果复制到其他工作表 [英] How to search a certain value and copy the results to other sheet with VBA

问题描述

我正在尝试编写VBA代码. 我在excel中有2个工作表:

I'm trying to write a VBA code. I have 2 worksheets in excel:

第一张纸:包含我所有工作结果的初始化结果"

First Sheet: "Results for init" that contain all my work results

第二张纸:结果标签"

Second Sheet: "Result tab"

我正在尝试在称为Id的第一行中找到某个值，在Id列中找到两个特定的ID号(86和66)，然后将结果(位于右侧的两列)复制到第二个工作表中一个特定的细胞.

I'm trying to find certain value in first row called Id ,find two certain ID numbers (86 and 66) in Id column and copy results (that located two columns from right) to the second sheet in a specific cells.

谢谢大家！

推荐答案

尝试一下

Sub Demo()
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcLR As Long, destLR As Long
    Dim cel As Range, srcRng As Range

    Set srcSht = ThisWorkbook.Sheets("Results for init")
    Set destSht = ThisWorkbook.Sheets("Result Tab")

    With srcSht
        srcLR = .Cells(.Rows.Count, "C").End(xlUp).Row  'get last row with data in srcSht using Column C
        Set srcRng = .Range("C2:E" & srcLR)             'set range for look up
    End With

    With destSht
        destLR = .Cells(.Rows.Count, "A").End(xlUp).Row 'get last row with data in destSht using Column A

        For Each cel In .Range("A2:A" & destLR)         'loop through each cell in Column a of destSht
            cel.Offset(0, 1).Value = WorksheetFunction.VLookup(cel, srcRng, 3, False)   'get vlookup result
        Next cel
    End With
End Sub

使用公式的解决方案:

Solution using formula :

假设您的数据在工作表Results for init中，然后在工作表Result tab的Cell B2中输入以下公式

Assuming your data is in sheet Results for init then in Cell B2 of sheet Result tab enter the following formula

=VLOOKUP(A2,'Results for init'!$C$2:$E$10,3,FALSE)

或

=INDEX('Results for init'!$E$2:$E$10,MATCH(Sheet3!A2,'Results for init'!$C$2:$C$10,0))

根据需要拖动/复制公式.根据您的数据更改范围2:10.参见图片以供参考.

Drag/Copy down formula as required. Change ranges 2:10 as per your data. See image for reference.

结果"标签工作表

这篇关于如何使用VBA搜索特定值并将结果复制到其他工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！