Hallo Max,
ja da war noch ein Fehler drin. Hier die verbesserte Version:
Sub suche()
Dim varZone As Variant
Dim varSuche As Variant
Dim varShip As Variant
Dim lngLZeile As Long
Dim lngLSpalte As Long
Dim s As Long
Dim sc As Long
Dim z As Long
Dim i As Long
Dim u As Long
'Daten aus Tabellenblatt Eingabe in Feld einlesen
With Worksheets("Eingabe")
lngLZeile = .Cells(Rows.Count, 1).End(xlUp).Row 'Letzte Zeile in Spalte A
lngLSpalte = .Cells(1, Columns.Count).End(xlToLeft).Column 'Letzte Spalte in Zeile 1
varSuche = .Range(.Cells(1, 1), .Cells(lngLZeile, lngLSpalte)) 'Daten in Feld einlesen
End With
'Zonen in Feld einlesen
With Worksheets("Zonen")
varZone = .UsedRange
End With
'ShipCost in Feld einlesen
With Worksheets("ShpCost")
lngLZeile = .Cells(Rows.Count, 1).End(xlUp).Row 'Letzte Zeile in Spalte A
lngLSpalte = .Cells(1, Columns.Count).End(xlToLeft).Column 'Letzte Spalte in Zeile 1
varShip = .Range(.Cells(1, 1), .Cells(lngLZeile, lngLSpalte)) 'Daten in Feld einlesen
End With
'Suchfeld durchlaufen
For s = 2 To UBound(varSuche, 1)
'Zone durchsuchen
For z = 2 To UBound(varZone, 1)
If varSuche(s, 2) >= varZone(z, 2) And varSuche(s, 2) <= varZone(z, 3) Then
If varSuche(s, 3) = varZone(z, 4) Then
If varSuche(s, 4) >= varZone(z, 5) And varSuche(s, 4) <= varZone(z, 6) Then
'gefundene Daten werden per Schleife in Suchfeld übertragen
For i = 5 To 11
varSuche(s, i) = varZone(z, i + 2)
Next i
End If
End If
End If
Next z
'ShpCost durchlaufen
For sc = 2 To UBound(varShip, 1)
For i = 12 To 16 Step 2
If varSuche(1, i) = varShip(sc, 1) Then
If varSuche(s, 19) = varShip(sc, 2) Then
If varSuche(s, 18) >= varShip(sc, 3) And varSuche(s, 18) <= varShip(sc, 4) Then
For u = LBound(varShip, 2) To UBound(varShip, 2)
If varSuche(s, i - 7) = varShip(1, u) Then
varSuche(s, i) = varShip(sc, u)
varSuche(s, i + 1) = varShip(sc, 5)
Exit For
End If
Next u
End If
End If
End If
Next i
Next sc
Next s
'Daten ausgeben
With Worksheets("Eingabe")
For s = 2 To UBound(varSuche, 1)
For i = 5 To 17
.Cells(s, i) = varSuche(s, i)
Next i
Next s
End With
End Sub
Schau mal, ob alle gewünschten Daten gefunden werden.
Gruß
M.O.