Multiple Criteria Match/Index VBA Across two sheets

huangapple go评论83阅读模式
英文:

Multiple Criteria Match/Index VBA Across two sheets

问题

以下是翻译好的部分:

Sheet 1看起来像这样:

Sheet 2看起来像这样:

我想要基于PO/SO和Activity匹配评论部分,而不是使用公式,而是使用VBA。

以下是我尝试编写的代码,但它不起作用...

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

如果我尝试运行这段代码,我不会收到任何错误警告,但也不会发生其他任何事情...没有错误消息,没有任何反应。我仔细检查了所有名称、列号和一切。

英文:

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

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

Sheet 1 looks like this:

Multiple Criteria Match/Index VBA Across two sheets

Sheet 2 looks like this:
Multiple Criteria Match/Index VBA Across two sheets

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

答案1

得分: 2

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

To

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

英文:

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

To

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

答案2

得分: 2

你好 Emma,假设你的表格1和表格2具有相同的列排列。

Sub findMatch()

Dim ID As String
Dim 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, 4).Value = ThisWorkbook.Worksheets("Sheet1").Cells(r, 3).Value
    End If

    Next s
Next r

End Sub

这是你上面提供的代码,对我来说完全正常。我只对这一行进行了轻微更改,以便自己测试:

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

这是我的工作簿,有表格1和表格2。不过,我要提醒你,按照这个顺序查找匹配可能会有问题。我更愿意使用查找功能并循环使用表格2。Multiple Criteria Match/Index VBA Across two sheets

Multiple Criteria Match/Index VBA Across two sheets

英文:

Hi Emma Assuming your sheet 1 and your sheet 2 have the same column lineup.

Sub findMatch()

Dim ID As String
Dim 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, 4).Value = ThisWorkbook.Worksheets("Sheet1").Cells(s, 3).Value
    End If

    Next s
        Next r
End Sub

This is the code you presented above and it worked just fine for me. I made a minor change to test for myself just on this line.

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

here is my workbook. sheet 1 and sheet 2. I will caution, however, that looking for a match in this order could be troublesome. I would use much rather use a find function and loop sheet 2.Multiple Criteria Match/Index VBA Across two sheets

Multiple Criteria Match/Index VBA Across two sheets

huangapple
  • 本文由 发表于 2020年1月6日 23:50:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/59615083.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定