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

0
votes

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.

2
excelvbauserform
Can you share a snippit of the userform? might help make some sense. I believe I'm reading that you have text boxes where a Part Number and Lot Number are input, then you have outputs of quantity in stock (i get that from the first paragraph; the end of the post seems to contradict that). Not sure which are text boxes, versus Comboboxes/etc. – Cyril
Also, general thing... i'd recommend Index/Match over VLookUp. – Cyril
Sorry about that, I added the images. Should have added those in the first place. – Luke P

2 Answers

0
votes

You mention that your lookup range has numbers. Are these numeric numbers or text numbers? If they are numeric numbers, then your vLookup may be failing because your combo is returning a text version of the number and a lookup for a text number in a numeric number range won't work. You could try putting eg CInt( ) around the combo value in the first lookup.

I would also try to cut the problem down more.

Get your combo value into a variable first so that you can step through the code and inspect the value to make sure that it is OK before you do the vlookup.

You are declaring most of your variables which is good, but you have not declared IndexNumber.

Regards

Paul Simon

0
votes

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.