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 data is in worksheet named "Source"

This formula returns the result when I tested it in Excel. However, the formula returns "#N/A" when run in VBA because the output formula added "@" in front of the lookup data sheet name "Source" as given below:

=INDEX(Source!$J:$J, MATCH(1,($C5=@Source!$C:$C)*($D5=@Source!$D:$D),0))

 

Can anyone advise how to fix the problem/issue?

 

My VBA codes are as follows:

 

Dim lastrow As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Source")
With ThisWorkbook.Worksheets("Dest")

     lastrow = .Cells(Rows.Count, 2).End(xlUp).row

     .Range("J5").Formula = "=Index(Source!$J:$J, MATCH(1,($C5=Source!$C:$C)* _      ($D5=Source!$D:$D),0))"

      .Range("J5").AutoFill Range("J5:J" & lastrow)

End With

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

14 Replies

  • 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.

         

Resources