(Solved) – Multiple Criteria Match/Index VBA Across two sheets


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

I am trying to convert a multi criteria index/match formula to vba code. This is the formula

{=IFNA(INDEX(Sheet1!A:C, MATCH(1, (Sheet1!A:A = Sheet2!A2) * (Sheet1!B:B = Sheet2!B2),0),3)," ")}

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

Sheet 1 looks like this:

enter image description here

Sheet 2 looks like this:
enter image description here

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…Any help would be appreciated

Dim ID As String 
Dim Act As String

For r = 2 To ThisWorkbook.Worksheets("Sheet 1").UsedRange.Rows.Count
    ID = ThisWorkbook.Worksheets("Sheet 1").Cells(r, 1).Value
    Activity = ThisWorkbook.Worksheets("Sheet 1").Cells(r, 2).Value

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

    If ThisWorkbook.Worksheets("Sheet 2").Cells(s, 1).Value = ID And ThisWorkbook.Worksheets("PCAM Commitments").Cells(s, 9).Value = Activity Then
        ThisWorkbook.Worksheets("Sheet 2").Cells(s, 3).Value = hisWorkbook.Worksheets("Previous PCAM Commitments").Cells(s, 3).Value
    End If

    Next r
        Next s

Leave a Reply

Your email address will not be published. Required fields are marked *