mit Scripting.Dictionary
25.03.2015 11:07:51
Erich
Hi Gregor,
das hier sollte recht flott gehen:
Option Explicit
Sub Dict_Min()
Dim myDict As Object, arW, zz As Long, strK As String
Dim arrK, arE()
Set myDict = CreateObject("Scripting.Dictionary")
With Sheets("Quelle")
arW = .Cells(2, 1).Resize(.Cells(.Rows.Count, 2).End(xlUp).Row - 1, 3)
End With
For zz = 1 To UBound(arW)
If arW(zz, 1) = "" Then arW(zz, 1) = arW(zz - 1, 1)
strK = arW(zz, 1) & "|" & arW(zz, 2)
If myDict.Exists(strK) Then
' neuer Wert ist kleiner
If myDict(strK) > arW(zz, 3) Then myDict(strK) = arW(zz, 3)
Else
myDict(strK) = arW(zz, 3)
End If
Next zz
With Sheets("Ziel")
arW = .Cells(2, 1).Resize(.Cells(.Rows.Count, 2).End(xlUp).Row - 1, 2)
ReDim arE(1 To UBound(arW), 0)
For zz = 1 To UBound(arW)
If arW(zz, 1) = "" Then arW(zz, 1) = arW(zz - 1, 1)
strK = arW(zz, 1) & "|" & arW(zz, 2)
If myDict.Exists(strK) Then
arE(zz, 0) = myDict(strK)
Else
arE(zz, 0) = "### fehlt ###"
End If
Next zz
.Cells(2, 3).Resize(UBound(arW)) = arE
End With
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich