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

14 Replies
best response confirmed by Doris1785 (Copper Contributor)
Solution

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

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``````

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

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.

# Apply index with multiple matches to table

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

# Re: Apply index with multiple matches to table

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.

``````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``````

# Re: Apply index with multiple matches to table

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.

# Re: Apply index with multiple matches to table

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.

# Re: Apply index with multiple matches to table

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")

# Re: Apply index with multiple matches to table

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?

# Re: Apply index with multiple matches to table

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.

# Re: Apply index with multiple matches to table

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.

# Re: Apply index with multiple matches to table

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.

# Re: Apply index with multiple matches to table

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

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
Set rng2 = lookupTbl.ListColumns("Purchase order").DataBodyRange
Set rng3 = lookupTbl.ListColumns("Item number").DataBodyRange

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``````

# Re: Apply index with multiple matches to table

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.

# Re: Apply index with multiple matches to table

@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

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

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

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

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

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.