Forum Discussion

Doris1785's avatar
Doris1785
Copper Contributor
Jun 07, 2022
Solved

Insert formula to a cell using VBA and autofill formula to the cells below

Hi everyone, I want to use VBA code to insert the formula ""=Index(Source!$J:$J, MATCH(1,($C5=Source!$C:$C)*($D5=Source!$D:$D),0))" into cell J5 in a destination worksheet named "Dest". The lookup ...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 07, 2022

    Doris1785 

    Try this:

    Sub Test()
    Dim therow As Long
    Dim lastrow As Long
    Application.ScreenUpdating = False
    With ThisWorkbook.Worksheets("Dest")
        lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row
        For therow = 5 To lastrow
            .Cells(therow, 10).Value = Evaluate( _
                "=INDEX(Source!$J:$J, MATCH(1,(Source!$C:$C=$C" & _
                therow & ")*(Source!$D:$D=$D" & therow & "),0))")
        Next therow
    End With
    Application.ScreenUpdating = True
End Sub
HansVogelaar's avatar
HansVogelaar
MVP
Jun 07, 2022

Doris1785 

Try this:

Sub Test()
    Dim therow As Long
    Dim lastrow As Long
    Application.ScreenUpdating = False
    With ThisWorkbook.Worksheets("Dest")
        lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row
        For therow = 5 To lastrow
            .Cells(therow, 10).Value = Evaluate( _
                "=INDEX(Source!$J:$J, MATCH(1,(Source!$C:$C=$C" & _
                therow & ")*(Source!$D:$D=$D" & therow & "),0))")
        Next therow
    End With
    Application.ScreenUpdating = True
End Sub
  • Doris1785's avatar
    Doris1785
    Copper Contributor
    Jun 08, 2022
    Hi Hans,
    I appreciated your reply and thank you so much for providing the solution which I have tested and it works. I learned something new (the EVALUATE function) from you.
    • Doris1785's avatar
      Doris1785
      Copper Contributor
      Sep 18, 2022

      HansVogelaar,

       

      sorry to bother you again. I tried Index and match with multiple criteria to tables and got "Application-defined or object-defined error". 

      Table1 is the result table to update the "Line No." column if the row matches both "PO Number" and "Item Number" in the lookup table "AtlasReport_1_Table_1"

       

      Here are my VBA codes. I have also attached the file with both tables and VBA codes.

      Private Sub IndexMatch()

       

      Dim wsTbl As ListObject

      Dim matchTbl As ListObject

      Dim rw As Long

       

      Set wsTbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")

      Set matchTbl = ThisWorkbook.Worksheets("PO_Line_details").ListObjects("AtlasReport_1_Table_1")

       

      For rw = wsTbl.DataBodyRange.Rows.Count To 1 Step -1

       

          If Not IsEmpty(wsTbl.DataBodyRange(rw, 1)) Then

          wsTbl.DataBodyRange(rw, 2).Value = Application.WorksheetFunction.Index(matchTbl.ListColumns("Line No").DataBodyRange, _

          Application.WorksheetFunction.Match(wsTbl.DataBodyRange(rw, 1).Value, matchTbl.ListColumns("Purchase order").DataBodyRange, 0), _

          Application.WorksheetFunction.Match(wsTbl.DataBodyRange(rw, 3).Value, matchTbl.ListColumns("Item number").DataBodyRange, 0))

                  

          End If

      Next

       

      End Sub

       

      Thank you in advance.

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 18, 2022

        Doris1785

        You didn't "translate" the formula to WorksheetFunction entirely correctly, but anyway, it cannot handle such a complicated formula. Moreover, VBA doesn't work well with structured table references.

        Try this instead

        Private Sub IndexMatch()
    Dim wsTbl As ListObject
    Dim rw As Long
    Set wsTbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    For rw = wsTbl.DataBodyRange.Rows.Count To 1 Step -1
        If wsTbl.DataBodyRange(rw, 1).Value <> "" Then
            wsTbl.DataBodyRange(rw, 2).Value = _
                Evaluate("INDEX(AtlasReport_1_Table_1[Line No], " & _
                "MATCH(1,(AtlasReport_1_Table_1[Purchase order]=""" & _
                wsTbl.DataBodyRange(rw, 1).Value & _
                """)*(AtlasReport_1_Table_1[Item number]=""" & _
                wsTbl.DataBodyRange(rw, 3).Value & """),0))")
        End If
    Next rw
End Sub

Resources