Running into errors with Choose/vlookup functions to choose between lookup tables based on Userform Input

Question

I am setting up a VBA userform function for my work that shows available quantities left to order based on part number and then lot number. When I use the Choose function combined with Vlookup in normal excel cells, the function works perfectly. When I try to convert it to VBA and incorporate the Combobox/textboxes to look it up, it errors out or doesn't return anything to the designated textboxes. The index table has two columns, one with the part number, and one with the index number. The other tables have alias (short names for lots), lots, and quantities. They are separated by part numbers. Typically I just receive a '1004' error : "Unable to get the VLookup property of the WorksheetFunction class". I understand that this is a very typical error for when vlookup can't find the value it's looking for, but I've been testing specifically with values that I know are on the tables I've set up. Once I have it actually working, I know how to put in the function to test if the value is actually there and put an error message and so on. It typically errors out on the first vlookup search. Private Sub txtPPAlias_Change() Dim lot As String Dim qty As String Dim itemnumber As String Dim PPIndex As Range Set PPIndex = Worksheets("Lookup").Range("R2:S5") 'This looks up the values for the lot number and available quantity. Close to working but not quite. 'Makes sure that the ComboBox has a value, and the length of the Alias is 2 or more If cboItemNumber.Value <> "" And Len(txtPPAlias) >= 2 Then 'Pulls Index number off lookup table IndexNumber = WorksheetFunction.VLookup(cboItemNumber.Value, PPIndex, 2, False) 'Looks for lot number from one of four tables based on Index Number pulled from previous argument lot = Choose(IndexNumber, WorksheetFunction.VLookup(txtPPAlias.Value, PET75DTable, 2, False), WorksheetFunction.VLookup(txtPPAlias.Value, PET95ATable, 2, False), WorksheetFunction.VLookup(txtPPAlias.Value, PET70DTable, 2, False), WorksheetFunction.VLookup(txtPPAlias.Value, PET60DTable, 2, False)) 'Looks for quantity based on same argument as previous lookup qty = Choose(IndexNumber, WorksheetFunction.VLookup(txtPPAlias.Value, PET75DTable, 3, False), WorksheetFunction.VLookup(txtPPAlias.Value, PET95ATable, 3, False), WorksheetFunction.VLookup(txtPPAlias.Value, PET70DTable, 3, False), WorksheetFunction.VLookup(txtPPAlias.Value, PET60DTable, 3, False)) 'Sets Userform lot textbox to lot value txtLotNumber.Value = lot 'Sets Userform qty textbox to qty value txtAvailableQuantity.Value = qty End If End Sub The txtLotNumber.Value is supposed to show the full lot number, and the txtAvailableQuantity.Value is supposed to return the quantity value from the table. I haven't gotten it to the point where it provides an output yet. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

Try a select case, rather than Choose, similar to (assumes columns are A through L; also assumes you're working with only those 4 items): Dim a as long With Sheets("Lookup") Select Case ItemNumber Case 14901 a = 1 Case 14899 a = 4 Case 14892 a = 7 Case 14886 a = 10 End Select txtLotNumber.Value = application.index(.columns(a+1),application.match(txtPPAlias.Value,.columns(a),0)) txtAvailableQuantity.Value = application.index(.columns(a+2),application.match(txtPPAlias.Value,.columns(a),0)) End With If you have more than just those 4 products, I would recommend a few things, starting with not merging your cells for labels. You could use Find to determine a column, then use that column number to dictate other columns. The above could be simplified further such that: dim fnd as long With Sheets("Lookup") fnd = .rows(1).find(What:=ItemNumber, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False).column txtLotNumber.Value = application.index(.columns(fnd+1),application.match(txtPPAlias.Value,.columns(fnd),0)) txtAvailableQuantity.Value = application.index(.columns(fnd+2),application.match(txtPPAlias.Value,.columns(fnd),0)) End With Assuming you have your Item Numbers where you have your product names, where you aren't merged (just to ensure that the correctvalues are found in the correct columns). Note that both bits of code are untested.