Optimierung Step2
13.12.2011 16:55:34
Rudi
Hallo,
das kann ich nicht testen:
Sub Vergleich2()
Dim oVergleich As Object, arrVergleich, lngI As Long
Dim strFile1 As String, strFile2 As String, wkb As Workbook
Dim strDat As String, vntMatch
Dim arrErg(), vntTmp, arrKeys, arrItems
Dim oShp As Shape
Const strBeide As String = "In beiden Dateien vorhanden"
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Datei 1 auswählen"
.AllowMultiSelect = False
.InitialFileName = "*.xls"
If .Show = -1 Then
strFile1 = .SelectedItems(1)
End If
End With
If strFile1 = "" Then
MsgBox "Abbruch"
Exit Sub
End If
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Datei 2 auswählen"
.AllowMultiSelect = False
.InitialFileName = "*.xls"
If .Show = -1 Then
strFile2 = .SelectedItems(1)
End If
End With
If strFile2 = "" Then
MsgBox "Abbruch"
Exit Sub
End If
Application.ScreenUpdating = False
Set oVergleich = CreateObject("Scripting.dictionary")
oVergleich("Nr.") = Array("Ergebnis", "F", "G")
Set wkb = Workbooks.Open(strFile1)
strDat = "In " & wkb.Name & " vorhanden"
With wkb.Sheets(1)
For Each oShp In .Shapes
oShp.Delete
Next
.Rows("1:13").Delete
arrVergleich = .Range(.Cells(1, 5), .Cells(1, 5).End(xlDown)).Resize(, 3)
End With
For lngI = LBound(arrVergleich) To UBound(arrVergleich)
vntMatch = arrVergleich(lngI, 1)
If IsNumeric(vntMatch) Then vntMatch = vntMatch + 0
oVergleich(vntMatch) = Array(strDat, arrVergleich(lngI, 2), arrVergleich(lngI, 3))
Next
wkb.Close False
Set wkb = Workbooks.Open(strFile2)
strDat = "In " & wkb.Name & " vorhanden"
With wkb.Sheets(1)
.Rows("1:9").Delete
.Cells.Find(what:="Mandant", LookIn:=xlValues, lookat:=xlWhole).EntireRow.Delete
For lngI = .Cells(Rows.Count, 4).End(xlUp).Row To 1 Step -1
If .Cells(lngI, 4) = "Kalender" Then .Cells(lngI, 1).Resize(4).EntireRow.Delete
Next lngI
arrVergleich = .Range(.Cells(1, 5), .Cells(Rows.Count, 5).End(xlUp)).Resize(, 3)
End With
For lngI = LBound(arrVergleich) To UBound(arrVergleich)
vntMatch = arrVergleich(lngI, 1)
If IsNumeric(vntMatch) Then vntMatch = vntMatch + 0
If oVergleich.exists(vntMatch) Then
vntTmp = oVergleich(vntMatch)
vntTmp(0) = strBeide
oVergleich(vntMatch) = vntTmp
Else
oVergleich(vntMatch) = Array(strDat, arrVergleich(lngI, 3), "")
End If
Next
wkb.Close False
arrKeys = oVergleich.Keys
arrItems = oVergleich.Items
ReDim arrErg(1 To oVergleich.Count, 1 To 4)
For lngI = 0 To oVergleich.Count - 1
arrErg(lngI + 1, 1) = arrKeys(lngI)
arrErg(lngI + 1, 2) = arrItems(lngI)(0)
arrErg(lngI + 1, 3) = arrItems(lngI)(1)
arrErg(lngI + 1, 4) = arrItems(lngI)(2)
Next
With Worksheets.Add
.Cells(1, 1).Resize(oVergleich.Count, 4) = arrErg
.Columns.AutoFit
.Cells(1, 1).Sort key1:=.Cells(2, 1), order1:=xlAscending, header:=xlYes
End With
End Sub
Gruß
Rudi