英文:
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:
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。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论