User Profile
Doris1785
Copper Contributor
Joined Jun 06, 2022
User Widgets
Recent Discussions
Re: Insert formula to a cell using VBA and autofill formula to the cells below
@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.45KViews0likes0CommentsRe: Insert formula to a cell using VBA and autofill formula to the cells below
HansVogelaar 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.46KViews0likes3CommentsRe: Insert formula to a cell using VBA and autofill formula to the cells below
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.46KViews0likes5CommentsRe: Insert formula to a cell using VBA and autofill formula to the cells below
HansVogelaar 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")46KViews0likes7CommentsRe: Insert formula to a cell using VBA and autofill formula to the cells below
HansVogelaar 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.46KViews0likes9CommentsRe: Insert formula to a cell using VBA and autofill formula to the cells below
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.46KViews0likes11CommentsRe: VBA date formatting regardless of regional setting
HansVogelaar, I tested your proposed solution with a few different desktop regional settings. It gave the same result, a consistent date in dd/mm/yyyy format that I'm looking for. Thank you for this quick and simple solution.2.1KViews0likes0CommentsVBA date formatting regardless of regional setting
Here is an extract of my vba codes: Dim dtToday As Date Dim CurrTime As String Dim Rng As Range dtToday = Date() 'today date CurrTime = Time() 'current time Rng.Offset(1).Value = Format(DateValue(dtToday), "m/d/yyyy") & " " & _ Application.WorksheetFunction.Text(CurrTime, "hh:mm:ss") And the output is "6-Jul-2022 10:32:36 AM" as I apply custom formatting to the cell in Excel as follows: [$-en-US]d-mmm-yyyy h:mm:ss AM/PM Currently it works fine if regional setting in PC is "English (United States)" If another user with different regional setting runs the vba code, the output becomes "7-Jun-2022 10:32:36 AM". To avoid this issue, how do I code or format date in vba to get the correct date regardless of regional settings? Thank you.Solved2.4KViews0likes2CommentsInsert 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 WithSolved51KViews0likes14Comments
Recent Blog Articles
No content to show