Simple VBA Problem Lookup gives out random data

Question

I'm a complete beginner and got probably a simple problem but I cant find any results in the internet.

I got a Userform to input a material number. Then I use a lookup to get the matching stock level, the consumption and the last goods receipt. This all works when I type in a correct Material Number. If I type in a random number, which the Macro normally should not match, it just puts out random data. Like consumption of the last correct Material Number for example.

Private Sub CommandButton1_Click()

UserForm1.Caption = "Eingabe"

Var1 = TextBox1.Value 'Msg Box wird als Variable festgelegt

    VBestand = Application.WorksheetFunction.Lookup(Var1, Worksheets("Bestand").Range("A2:C4"), Worksheets("Bestand").Range("B2:B5")) 'VBestand wird als Variable für den Sverweis festegelegt. Es wird nach Var1 gesucht
    VVerbrauch = Application.WorksheetFunction.Lookup(Var1, Worksheets("Verbrauch").Range("A2:C4"), Worksheets("Verbrauch").Range("B2:B5"))
    VWareneingang = Application.WorksheetFunction.Lookup(Var1, Worksheets("Wareneingang").Range("A2:C4"), Worksheets("Wareneingang").Range("B2:B5"))
    VReichweite = VBestand / VVerbrauch
    
Dim lZeile As Integer

    lZeile = ActiveSheet.Cells(1048576, 1).End(xlUp).Row + 1
    Cells(lZeile, 2) = VBestand 'In der untersten Zelle wird der Bestand ausgegeben
    Cells(lZeile, 1) = Var1 'In der untersten Zelle wird das gesuchte Material ausgegeben
    Cells(lZeile, 3) = VVerbrauch 'In der untersten Zelle wird der Bestand ausgegeben
    Cells(lZeile, 4) = VReichweite
    Cells(lZeile, 5) = VWareneingang
Unload Me
VWareneingang = resetVWareneingang
End Sub

This is my code. I thought that the data is just saved from the last check, that's why I tried "reset" in the end. But it also doesn't help.

My goal is that If there is a wrong Material Number, which the Macro cant find, it gives out a massage box and end the macro.

question from:https://stackoverflow.com/questions/66060479/simple-vba-problem-lookup-gives-out-random-data

Answer 1

You using a Lookup (the excel worksheet function). This function will return a the closest value in the lookup array if no exact match is found.
When writing formulas in Excel(You are using excel worksheet function in VBA) rather use an nested Index / Match formula to ensure exact match.

Also be careful using a vlookup - the data tables needs to be sorted correctly