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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…