Index Match VBA to update on rows-CodePudding

I have this code and I would like it to work on the next row till last row,

Sub Index_Match()
  Dim result As Variant
  Range("P3").Value = [INDEX('Sheet1'!K:K,MATCH(1,(L3='Sheet1'!G:G)\*(Q3='Sheet1'!J:J),0))]
  Debug.Print result
End Sub

The code has to update on every row.

  Range("P4").Value = [INDEX('Sheet1'!K:K,MATCH(1,(L4='Sheet1'!G:G)\*(Q4='Sheet1'!J:J),0))]

So on.....

Any help appreciated

CodePudding user response：

Evaluate Index/Match Array Formula

Sub EvaluateIndexMatchFormula()

    ' Write the formula to a string first...    
    Dim Formula As String: Formula _
        = "=IFERROR(INDEX('Sheet1'!K:K," _
        & "MATCH(1,(L3='Sheet1'!G:G)*(Q3='Sheet1'!J:J),0)),"""")"
    ' ... so you can test it with:
    'Debug.Print Formula
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet2") ' adjust!

    Dim drg As Range ' Destination Range

    With dws.UsedRange
        Set drg = dws.Range("P3", dws.Cells(.Rows.Count   .Row - 1, "P"))
    End With
    
    With drg
        ' Write formulae. 
        With .Cells(1)
            .FormulaArray = Formula
            .AutoFill drg, xlFillDefault
        End With
        ' Keep only values.
        .Value = .Value
    End With
 
End Sub