Multiple Criteria Match/Index VBA Across two sheets

Question

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:

Sheet 2 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

What does `Not Working` mean. are you getting an error, if so which line? Is the Output wrong, how so? — Scott Craner, Jan 06 '20 at 16:14
@Scott Craner Sorry about the ambiguity. I edited my question and code. It should be clearer now. — Beans, Jan 06 '20 at 16:24
Your next are reversed. `Next s` should be before `Next r` as `s` is the inner loop. — Scott Craner, Jan 06 '20 at 16:26
@Scott Craner So I edited my question again... I reversed r and s, this time. I didn't get any error pop-ups, but nothing else happened either. — Beans, Jan 06 '20 at 16:32
Then it is a data problem. Make sure the data does not have extra spaces that would keep excel from finding the text strings as matches. Also make sure the column numbers are correct. There are a lot of things it could be now. You need to step through the code and make sure each line is returning what you think it should. — Scott Craner, Jan 06 '20 at 16:36
@EmmaG See: https://stackoverflow.com/questions/27604084/insert-an-array-formula-via-vba You can use `.FormulaArray` to insert your existing, working formula. It would work for VBA and can be applied to the range of first to last cell in Sheet2. You could even follow up with `.value=.value` if you want to hide the formulas. — Cyril, Jan 06 '20 at 16:57
I went through my code many times, I really didn't find any spelling errors and since I only have 3 columns, I am 100% positive that I didn't make mistakes on column numbers. Help... — Beans, Jan 06 '20 at 16:57
`ThisWorkbook.Worksheets("Sheet2").Cells(s, 3).Value = ThisWorkbook.Worksheets("Sheet1").Cells(r, 3).Value` instead? — JNevill, Jan 06 '20 at 17:55
No doubt. The issue is definitely in your text comparison (and some slight difference in values), but the suggested change is still needed for this code to make sense. — JNevill, Jan 06 '20 at 18:50
@JNevill Sorry disregard my previous comment. It actually made a big difference. You were right — Beans, Jan 06 '20 at 23:51

score 2 · Accepted Answer · answered Jan 06 '20 at 17:56

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

answered Jan 06 '20 at 17:56

GMalc

2,608
1
9
16

Hey GMalc, thank you for the comment! I tried both ways, actually, it doesn't matter. Doesn't matter I use s or r there, the code will work either way. But I got my code works now...I think it didn't work before because I forgot to save the workbook before I run the VBA (at least that's the only reason I can think of), once I saved my workbook, my code worked – Beans Jan 06 '20 at 18:09
@EmmaG You must use the row variable for the correct worksheet or else you will copy the wrong value from the wrong row. e.g. If you use `s` for both parts, during the first loop for `Sheet2.Row(2)`, lets say you get a match on `Sheet1.Row(20)`, by using `s` you will be selecting the value at `Sheet1("C2")` not the value in `Sheet1("C20")`, thus you will be getting the wrong information for `Sheet2("C2")` – GMalc Jan 06 '20 at 20:55
you are correct. I just found out that if I use S in the second parenthesis, I will get wrong matching results. So s or r actually make a big difference. Thanks! – Beans Jan 06 '20 at 23:55

score 2 · Answer 2 · answered Jan 06 '20 at 17:58

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.

Hey Miguel, thanks for the comment! I explained the reason why my code didn't work before in another answer's comment section. But thanks for the suggestion, I never used find function in vba, so I will take a look — Beans, Jan 06 '20 at 18:14
Miguel, please fix your answer, the OP was not writing to Column D but Column C. Also, unless the match values are on the same row for each worksheet, you will get the wrong value from sheet1 if you are using the row number from sheet2. Please see my reply comment to the OP in my answer. — GMalc, Jan 06 '20 at 21:18
Hi Gmalc, everything you said in your comment I explained in my answer. Why I did it. `I will caution, however, that looking for a match in this order could be troublesome` — Miguel, Jan 07 '20 at 15:02

Multiple Criteria Match/Index VBA Across two sheets

2 Answers2

Linked