VLookup function - Referencing a table from another sheet

0
votes

I'm a complete novice with VBA and just need some help getting this code to work. Here is the code

Private Sub cmdGenerateCost_Click()

Dim ws As Worksheet
Set ws = Application.Workbooks(1).Worksheets(2)
Set productsTab = ws.Range("Products")
Application.WorksheetFunction.VLookup

frmAddNewOrder.lblCost.Caption = "Cost: $" & txtQuantity * VLookup(cboProduct, productsTab, 4)

End Sub

However I'm getting an "Argument not optional" error message.

What I need the code to do is for it to calculate the cost of an item using a table from a sheet that has the information, hence the Vlookup, and then change a label in a userform. The table in the sheet is called Products and it is in the 2nd worksheet called, Products. Any help would be greatly appreciated

1
excelvbafunctionvlookup

1 Answers

0
votes

the message is very clear, VLOOPUP is a function that requires some arguments. See https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

So you need to provide those arguments in the call. Something similar to

Let cost = Application.WorksheetFunction.VLookup("item", productsTab, 4, False)

that will find the item "item" in your range and will return the content of its fourth column. Change it accordingly to your needs.

you may also read https://support.office.com/en-us/article/VLOOKUP-What-it-is-and-when-to-use-it-5984e27b-4f0d-431e-83b1-7ab062c75493