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

[Doris1785]

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

[#2 · Accepted solution]

@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

[#3]

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.

[#4]

@Hans Vogelaar,

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.

[#5]

@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

[#6]

@Hans Vogelaar 

Your solution works. Thank you for the fixes. 

The only thing I cannot figure out in your codes is the 2 extra (") in the MATCH statements.

MATCH(1,(AtlasReport_1_Table_1[Purchase order]="""

Why are extra 2 (") signs require in the codes? Thank you in advance for sharing.

[#7]

@Doris1785 

the code uses Evaluate to calculate the result of a formula supplied in the form of a text string.

The text string is enclosed in double quotes "...", but the string itself also contains double quotes.

This would cause problems (where does the text string end if there's a " in it?)

So we double the " to "" to avoid that.

[#8]

@Hans Vogelaar 

I see. I have another question. I need to transfer the VBA codes to another workbook to be one of the sub procedures but the output ("Tables") will be in another workbook. If my VBA codes are in another workbook, I have to add the workbook name to the MATCH statement in my codes. As the given workbook and worksheet name are very long, can I dim and assign a name to my lookup table "AtlasReport_1_Table_1" in my INDEX and MATCH formula? And how do I do it as I agree with you that it is not that straightforward to reference structured table in VBA? Appreciate your help. 

Dim matchTbl As ListObject

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

[#9]

@Doris1785 

I'm a bit confused, can you set me right? Thanks in advance.

Will the code be (1) in the workbook that contains AtlasReport_1_Table_1, or (2) in the workbook that contains the output table, or (3) in a third workbook?

[#10]

The VBA codes will not be in the output file that I sent you with both tables, the output table ("Table1") and lookup table ("AtlasReport_1_Table_1") as the output file with the tables I received every month will always be changing or updated. Hence the VBA codes will be in a different workbook or file with other sub procedures that run the job.

[#11]

@Doris1785 

As far as I can tell, you only need to change the line

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

to

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

where ... is the name of the workbook containing the data. It should be open in Excel - if necessary, you can open it at the beginning of the macro.

[#12]

@Hans Vogelaar 

Do I have to specify the workbook or worksheet name for table "AtlasReport_1_Table_1"?

I got #VALUE! error when I run the code from another workbook named "Code file". I have attached the workbook with the VBA codes. The error seems to suggest that it can't find the lookup value.

[#13]

@Doris1785 

Thanks. Try this:

Private Sub CommandButton1_Click()
    Dim targetwb As Workbook
    Dim thisws As Worksheet
    Dim targetFN As String
    Dim wsTbl As ListObject, lookupTbl As ListObject
    Dim rw As Long
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Dim adr1 As String, adr2 As String, adr3 As String

    Set thisws = ThisWorkbook.Worksheets("Execute")
    targetFN = thisws.Range("C4").Value & "\" & thisws.Range("C3").Value & ".xlsx"

    Set targetwb = Workbooks.Open(targetFN)
    Set wsTbl = targetwb.Worksheets("Sheet1").ListObjects("Table1")
    Set lookupTbl = targetwb.Worksheets("PO_Line_Details").ListObjects("AtlasReport_1_Table_1")
    Set rng1 = lookupTbl.ListColumns("Line No").DataBodyRange
    adr1 = rng1.Address(External:=True)
    Set rng2 = lookupTbl.ListColumns("Purchase order").DataBodyRange
    adr2 = rng2.Address(External:=True)
    Set rng3 = lookupTbl.ListColumns("Item number").DataBodyRange
    adr3 = rng3.Address(External:=True)

    For rw = wsTbl.DataBodyRange.Rows.Count To 1 Step -1
        If wsTbl.DataBodyRange(rw, 1).Value <> "" Then
            wsTbl.DataBodyRange(rw, 2).Value = _
                Evaluate("INDEX(" & adr1 & ", " & _
                "MATCH(1,(" & adr2 & "=""" & _
                wsTbl.DataBodyRange(rw, 1).Value & _
                """)*(" & adr3 & "=""" & _
                wsTbl.DataBodyRange(rw, 3).Value & """),0))")
       End If
    Next rw
End Sub

[#14]

@Hans Vogelaar 

It works. Thank you so much for your help on providing the solution.

I learned from you how to code for structured table in vba.  Greatly appreciated.

Have a good weekend ahead!

[#15]

@Hans Vogelaar

Sorry to bother you again. I have added the "Quantity" field to the index match formula but I got #N/A error which I suspect that my double quotes in the Evaluate statement for the index match are incorrect but I do not know how to fix. My updated VBA codes are as follows:

Private Sub CommandButton1_Click()

    Dim targetwb As Workbook

    Dim thisws As Worksheet

    Dim targetFN As String

    Dim lookupTbl As ListObject

    Dim rw As Long, lastrow As Long

    Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range

    Dim adr1 As String, adr2 As String, adr3 As String, adr4 As String

    Set thisws = ThisWorkbook.Worksheets("Output")

    targetFN = thisws.Range("L3").Value & "\" & thisws.Range("L2").Value & ".xlsx"

    Set targetwb = Workbooks.Open(targetFN)

    Set lookupTbl = targetwb.Worksheets("PO_Line_details").ListObjects("AtlasReport_1_Table_1")

    Set rng1 = lookupTbl.ListColumns("Line No").DataBodyRange

    adr1 = rng1.Address(External:=True)

    Set rng2 = lookupTbl.ListColumns("Purchase order").DataBodyRange

    adr2 = rng2.Address(External:=True)

    Set rng3 = lookupTbl.ListColumns("Item number").DataBodyRange

    adr3 = rng3.Address(External:=True)

    Set rng4 = lookupTbl.ListColumns("Quantity").DataBodyRange

    adr4 = rng4.Address(External:=True)

    lastrow = thisws.Cells(Rows.Count, 1).End(xlUp).Row 

    For rw = 6 To lastrow

        If thisws.Cells(rw, 1).Value <> "" Then

            thisws.Cells(rw, 2).Value = _

                Evaluate("INDEX(" & adr1 & ", " & _

                "MATCH(1,(" & adr2 & "=""" & _

                thisws.Cells(rw, 1).Value & _

                """)*(" & adr3 & "=""" & _

                thisws.Cells(rw, 3).Value & _

                """)*(" & adr4 & "=""" & _

                thisws.Cells(rw, 4).Value & """),0))")

       End If

    Next rw

End Sub

I have attached both the read table and output file with the VBA codes.

Thank you in advance for your advice and help.