跨两页的多个条件匹配/索引VBA [英] Multiple Criteria Match/Index VBA Across two sheets

问题描述

同一工作簿中两张纸之间的多标准索引/匹配VBA

Multi Criteria Index/Match VBA across two sheets in the same workbook

因此，基本上，我在同一工作簿中有2张纸

So, basically, I have 2 sheets in a same workbook

第1张表格如下:

Sheet 2看起来像这样:

Sheet 2 looks like this:

我想使用VBA而不是公式根据PO/SO AND Activity匹配注释"部分.

I want to match the Comments section based on PO/SO AND Activity using VBA instead of formula.

下面是我尝试编写的代码，但无法正常工作...

Below is the code I tried to write, but it’s not working…

Dim ID As String, Activity As String

    For r = 2 To ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows.Count

    ID = ThisWorkbook.Worksheets("Sheet1").Cells(r, 1).Value
    Activity = ThisWorkbook.Worksheets("Sheet1").Cells(r, 2).Value

        For s = 2 To ThisWorkbook.Worksheets("Sheet2").UsedRange.Rows.Count

            If ThisWorkbook.Worksheets("Sheet2").Cells(s, 1).Value = ID And ThisWorkbook.Worksheets("Sheet2").Cells(s, 2).Value = Activity Then
                ThisWorkbook.Worksheets("Sheet2").Cells(s, 3).Value = ThisWorkbook.Worksheets("Sheet1").Cells(s, 3).Value
            End If

        Next s
    Next r

如果我尝试运行代码，将不会收到任何错误警告，但也不会发生任何其他事情……没有错误消息，没有任何反应.我仔细检查了所有名称，列号和所有内容

If I try to run the code, I won't get any error warnings, but nothing else would happen neither...no error message, no any reaction. I double checked all names, column numbers, and everything

推荐答案

我的代码没有问题，只不过您需要更改此行...

I had no problem with your code except you need to Change this line...

ThisWorkbook.Worksheets("Sheet2").Cells(s, 3).Value = ThisWorkbook.Worksheets("Sheet1").Cells(s, 3).Value

收件人

ThisWorkbook.Worksheets("Sheet2").Cells(s, 3).Value = ThisWorkbook.Worksheets("Sheet1").Cells(r, 3).Value

这篇关于跨两页的多个条件匹配/索引VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！