Forum Discussion
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
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.
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.
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 SubYour 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.
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.