索引匹配 VBA 以更新行-解网

问：

我有这个代码，我希望它在下一行工作，直到最后一行，

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

代码必须在每一行上更新。

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

等等.....

任何帮助表示赞赏

Excel VBA 索引匹配

计算索引/匹配数组公式

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

上一个：在匹配多个条件的范围内搜索行

下一个：使用循环将多个行项目索引/匹配到一个页面上

索引匹配 VBA 以更新行

Index Match VBA to update on rows

评论

计算索引/匹配数组公式