Vlookup, wenn nicht gefunden...
21.02.2018 11:12:19
Burak
also mein makro ermittelt über SVerweis aus einer zweispaltigen Liste den zugehörigen Wert zu jedem Barcode aus mehreren Tabellen.
Gelegentlich kommt ein Barcode vor, der in der zweispaltigen Liste (noch) nicht vorhanden ist, dann läuft das Makro in Error.
Bisher waren es immer Barcodes die mit einer 9 geendet haben. Dies habe ich über Modulo und Rest 9 rausgefiltert.
Jetzt kommen auch Barcodes mit einer 8 am Ende, die nicht in der Liste sind. Dies kann ich nicht über Modulo machen, da es Barcodes in der Liste gibt die mit einer 8 enden.
Mein Wunsch wäre es, dass er in der Schleife, wenn er den Barcode in der Liste nicht findet, einfach zum nächsten Barcode bzw. nächsten Step der Schleife springen soll.
Im optimalen Fall diesen nicht gefundenen Barcode iwo auflisten.
Die BarcodeListe sieht ungefähr so aus:
BarcodeLPMatrix
| A | B | |
| 1 | Code | LP-Nutzen |
| 2 | 1190 | 1 |
| 3 | 1270 | 1 |
| 4 | 1290 | 6 |
| 5 | 1500 | 1 |
| 6 | 1510 | 1 |
| 7 | 1610 | 2 |
| 8 | 1611 | 2 |
| 9 | 1612 | 2 |
| 10 | 1613 | 2 |
| 11 | 1628 | 2 |
| 12 | 1730 | 1 |
Eine Beispieltabelle mit den Barcodes:
Hilfstabelle
| A | B | C | D | E | F | |
| 1 | Barcode | Masterbarcode | Schicht | LP Nutzen | Datum | Uhrzeit |
| 2 | 1190 | 1190035347 | FS | 1 | 15.01.2018 | 06:27:25 |
| 3 | 1190 | 1190035345 | FS | 1 | 15.01.2018 | 06:27:43 |
| 4 | 1190 | 1190035343 | FS | 1 | 15.01.2018 | 06:28:09 |
| 5 | 1190 | 1190035340 | FS | 1 | 15.01.2018 | 06:28:40 |
| 6 | 7760 | 7760815533 | FS | 4 | 15.01.2018 | 13:14:24 |
| 7 | 7760 | 7760815745 | FS | 4 | 15.01.2018 | 13:14:49 |
| 8 | 7760 | 7760815729 | FS | 4 | 15.01.2018 | 13:15:15 |
| 9 | 7760 | 7760815489 | FS | 4 | 15.01.2018 | 13:15:42 |
| 10 | 7760 | 7760815493 | FS | 4 | 15.01.2018 | 13:16:10 |
| 11 | 7760 | 7760815497 | FS | 4 | 15.01.2018 | 13:18:45 |
| 12 | 3430 | 3430126543 | SS | 6 | 15.01.2018 | 17:08:39 |
| 13 | 3430 | 3430126537 | SS | 6 | 15.01.2018 | 17:09:47 |
| 14 | 3430 | 3430126531 | SS | 6 | 15.01.2018 | 17:11:14 |
| 15 | 3430 | 3430126525 | SS | 6 | 15.01.2018 | 17:12:07 |
Der Code:
Sub Ruestwechsel()
'Deklarationen der Variablen
Dim cntr As Long, c As Range, i As Long, Zeilenzahl As Long, Zeilenzahl2 As Long
Dim startdatum As Date, enddatum As Date, d As Variant, j As Long, n
Dim Summe, loLetzte As Long, loSpalte, pctCompl As Integer
'Gesuchtes Datum eingeben
startdatum = InputBox("Bitte gib ein Startdatum ein:")
enddatum = InputBox("Bitte gib ein Enddatum ein:")
Worksheets("Hilfstabelle").Cells.Clear
Worksheets("Linienauswertung - Grafiken").Cells.Clear
'Alle 5 Linien
For i = 2 To 6
With Worksheets("R" & i - 1)
'Rüstwechsel in Nachtschicht zählen und ausgeben
cntr = 0
For Each c In .Range("F3:F" & .Cells(.Rows.Count, 6).End(xlUp).Row)
If (c.Value = "NS" And c.Offset(, -5).Value c.Offset(-1, -5).Value And c.Offset(, 7). _
Value >= startdatum And c.Offset(, 7).Value = 0.91666) Or (c.Value = "NS" And c.Offset(, -5).Value c.Offset(-1, -5).Value And c.Offset(, 7).Value > startdatum And c.Offset(, 7).Value c.Offset(-1, -5).Value And c.Offset(, 7). _
Value >= startdatum And c.Offset(, 7).Value c.Offset(-1, -5).Value And c.Offset(, 7). _
Value >= startdatum And c.Offset(, 7).Value = startdatum And .Cells(n, 13). _
Value .Cells(n - 1, 1).Value Then
Summe = Summe + .Cells(n, 5).Value
End If
Next n
'Summierte Rüstzeiten in Ausgabeblatt schreiben
Worksheets("Linienauswertung - Grafiken").Range("B" & i + 7).Value = Summe
'Spätschicht
Summe = 0
For n = 3 To .Cells(Rows.Count, 5).End(xlUp).Row
If .Cells(n, 6).Value = "SS" And .Cells(n, 13).Value >= startdatum And .Cells(n, 13). _
Value .Cells(n - 1, 1).Value Then
Summe = Summe + .Cells(n, 5).Value
End If
Next n
'Summierte Rüstzeiten in Ausgabeblatt schreiben
Worksheets("Linienauswertung - Grafiken").Range("C" & i + 7).Value = Summe
'Nachtschicht
Summe = 0
For n = 3 To .Cells(Rows.Count, 5).End(xlUp).Row
If (.Cells(n, 14).Value > 0.9166666 And .Cells(n, 1).Value .Cells(n - 1, 1).Value _
And .Cells(n, 13).Value >= startdatum And .Cells(n, 13).Value .Cells(n - 1, 1).Value And .Cells(n, 13).Value > startdatum And .Cells(n, 13).Value "" Then
'Datum außerhalb Einträge entfernen
If .Range("E" & j).Value enddatum + 1 Then
.Rows(j).Delete
j = j - 1
'Einträge vor der ersten Frühschicht entfernen
ElseIf .Range("E" & j).Value = startdatum And .Range("F" & j).Value = 0.25 Then
.Rows(j).Delete
j = j - 1
End If
End If
Next j
'Doppelte Einträge entfernen
With Worksheets("Hilfstabelle")
'Letzte Zeile ermitteln
loLetzte = .Cells(.Rows.Count, 2).End(xlUp).Row
'Letzte Spalte ermitteln
loSpalte = .Cells(2, .Columns.Count).End(xlToLeft).Column + 1
.Range(.Cells(2, loSpalte), .Cells(loLetzte, loSpalte)).FormulaLocal = "=WENN(B1=B2;0; _
ZEILE())"
.Range(.Cells(1, loSpalte), .Cells(loLetzte, loSpalte)).Value = .Range(.Cells(1, _
loSpalte), .Cells(loLetzte, loSpalte)).Value
.Cells(1, loSpalte) = 0
.Range(.Cells(1, 1), .Cells(loLetzte, loSpalte)).RemoveDuplicates Columns:=loSpalte, _
Header:=xlNo
'Zellinhalte löschen
.Columns(loSpalte).ClearContents
End With
'Barcode aus Masterbarcode rauskopieren
Zeilenzahl = .Range("B1").CurrentRegion.Rows.Count
For Each d In .Range("B2:B" & Zeilenzahl)
If Len(d.Value) > 0 Then
.Cells(d.Row, 1).Value = Left(d.Value, 4)
End If
Next
'SVerweis über Barcode für "LP Nutzen" aus BarcodeLPMatrix
For j = 2 To Zeilenzahl
.Range("D" & j).Value = Application.WorksheetFunction.VLookup(.Range("A" & j).Value, _
Worksheets("BarcodeLPMatrix").Range("A2:B146"), 2, False)
Next j
'Überschriften setzen
.Range("A1").Value = "Barcode"
.Range("D1").Value = "LP Nutzen"
'Überschriften Fettdruck
.Rows(1).Font.Bold = True
End With
'Zeilen zählen
Zeilenzahl = Worksheets("Hilfstabelle").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Hilfstabelle")
'Leiterplatten zählen und eintragen
Worksheets("Linienauswertung - Grafiken").Range("B" & i + 14).Value = Application.SumIf(. _
Range("C2:C" & Zeilenzahl), "FS", .Range("D2:D" & Zeilenzahl))
Worksheets("Linienauswertung - Grafiken").Range("C" & i + 14).Value = Application.SumIf(. _
Range("C2:C" & Zeilenzahl), "SS", .Range("D2:D" & Zeilenzahl))
Worksheets("Linienauswertung - Grafiken").Range("D" & i + 14).Value = Application.SumIf(. _
Range("C2:C" & Zeilenzahl), "NS", .Range("D2:D" & Zeilenzahl))
'Nutzen zählen und eintragen
Worksheets("Linienauswertung - Grafiken").Range("B" & i + 21).Value = WorksheetFunction. _
CountIfs(.Range("C2:C" & Zeilenzahl), "FS")
Worksheets("Linienauswertung - Grafiken").Range("C" & i + 21).Value = WorksheetFunction. _
CountIfs(.Range("C2:C" & Zeilenzahl), "SS")
Worksheets("Linienauswertung - Grafiken").Range("D" & i + 21).Value = WorksheetFunction. _
CountIfs(.Range("C2:C" & Zeilenzahl), "NS")
End With
'Mikrostörungen zählen
With Worksheets("Mikrostörungen - Daten")
Zeilenzahl = Zeilenzahl2
Zeilenzahl2 = .Range("B" & Zeilenzahl2 + 4).End(xlDown).Row
Worksheets("Linienauswertung - Grafiken").Range("B" & i + 28).Value = WorksheetFunction. _
CountIfs(.Range("F" & Zeilenzahl + 1 & ":F" & Zeilenzahl2), "FS")
Worksheets("Linienauswertung - Grafiken").Range("C" & i + 28).Value = WorksheetFunction. _
CountIfs(.Range("F" & Zeilenzahl + 1 & ":F" & Zeilenzahl2), "SS")
Worksheets("Linienauswertung - Grafiken").Range("D" & i + 28).Value = WorksheetFunction. _
CountIfs(.Range("F" & Zeilenzahl + 1 & ":F" & Zeilenzahl2), "NS")
End With
Next i
Zeilenzahl = 0
Zeilenzahl2 = 0
With Worksheets("Linienauswertung - Grafiken")
For i = 2 To 6
'Störungen zählen
With Worksheets("Störungen - Daten")
Zeilenzahl = Zeilenzahl2
Zeilenzahl2 = .Range("B" & Zeilenzahl2 + 4).End(xlDown).Row
Worksheets("Linienauswertung - Grafiken").Range("B" & i + 35).Value = WorksheetFunction. _
CountIfs(.Range("F" & Zeilenzahl + 1 & ":F" & Zeilenzahl2), "FS")
Worksheets("Linienauswertung - Grafiken").Range("C" & i + 35).Value = WorksheetFunction. _
CountIfs(.Range("F" & Zeilenzahl + 1 & ":F" & Zeilenzahl2), "SS")
Worksheets("Linienauswertung - Grafiken").Range("D" & i + 35).Value = WorksheetFunction. _
CountIfs(.Range("F" & Zeilenzahl + 1 & ":F" & Zeilenzahl2), "NS")
End With
'Linienbezeichnungen eintragen
.Range("A" & i).Value = "R" & i - 1
.Range("A" & i + 7).Value = "R" & i - 1
.Range("A" & i + 14).Value = "R" & i - 1
.Range("A" & i + 21).Value = "R" & i - 1
.Range("A" & i + 28).Value = "R" & i - 1
.Range("A" & i + 35).Value = "R" & i - 1
'Tagesmengen summieren
.Range("E" & i + 14).Value = .Range("B" & i + 14).Value + .Range("C" & i + 14).Value + . _
Range("D" & i + 14).Value
.Range("E" & i + 21).Value = .Range("B" & i + 21).Value + .Range("C" & i + 21).Value + . _
Range("D" & i + 21).Value
Next i
'Tabellenbezeichnungen eintragen
.Range("A1").Value = "Rüstwechsel"
.Range("A8").Value = "Rüstwechselzeiten"
.Range("A15").Value = "Gesamtleistung LP"
.Range("A22").Value = "Gesamtleistung Nutzen"
.Range("A29").Value = "Mikrostörungen"
.Range("A36").Value = "Störungen"
'Datum eintragen
If startdatum enddatum Then
.Range("F8").Value = startdatum
.Range("G8").Value = "-"
.Range("H8").Value = enddatum
Else
.Range("F8").Value = startdatum
End If
'Schichten eintragen
.Range("B1").Value = "Frühschicht"
.Range("C1").Value = "Spätschicht"
.Range("D1").Value = "Nachtschicht"
.Range("B8").Value = "Frühschicht"
.Range("C8").Value = "Spätschicht"
.Range("D8").Value = "Nachtschicht"
.Range("B15").Value = "Frühschicht"
.Range("C15").Value = "Spätschicht"
.Range("D15").Value = "Nachtschicht"
.Range("E15").Value = "Tagesmenge"
.Range("B22").Value = "Frühschicht"
.Range("C22").Value = "Spätschicht"
.Range("D22").Value = "Nachtschicht"
.Range("E22").Value = "Tagesmenge"
.Range("B29").Value = "Frühschicht"
.Range("C29").Value = "Spätschicht"
.Range("D29").Value = "Nachtschicht"
.Range("B36").Value = "Frühschicht"
.Range("C36").Value = "Spätschicht"
.Range("D36").Value = "Nachtschicht"
'Spaltebreiten
.Columns("A:A").ColumnWidth = 21
.Columns("B:E").ColumnWidth = 13
'Tabellenüberschriften Fettdruck
.Rows(1).Font.Bold = True
.Rows(8).Font.Bold = True
.Rows(15).Font.Bold = True
.Rows(22).Font.Bold = True
.Rows(29).Font.Bold = True
.Rows(36).Font.Bold = True
End With
End Sub
'SVerweis über Barcode für "LP Nutzen" aus BarcodeLPMatrix
With Worksheets("Hilfstabelle")
For j = 2 To Zeilenzahl
.Range("D" & j).Value = Application.WorksheetFunction.VLookup(.Range("A" & j).Value, Worksheets( _
"BarcodeLPMatrix").Range("A2:B146"), 2, False)
Next j
End With
Die Barcodeliste befindet sich auf dem Tabellenblatt
"BarcodeLPMatrix"
Daten mit den Barcodes werden aus den folgenden 5 Tabellenblättern
"R1", "R2", "R3", "R4" und "R5"
in das Tabellenblatt
"Hilfstabelle"
kopiert und ausgewertet.
Vielen Dank im Voraus!