Hallo,
Du machst da auch irgendwie komisch rum, Du öffnest Deine Dateien ja zweimal und Du selektierst da unnötigerweise....
Set wb1 = Workbooks.Open(Datei1)
Set wb2 = Workbooks.Open(Datei2)
Workbooks.Open FileName:=Datei1
ActiveSheet.Range( "A1:M500" ). Select
Range( "A1:M500" ).Sort Key1:=Range( "A1" ), Order1:=xlAscending, Header:=xlNo
Selection.Replace " " , ""
Selection.Replace "." , ","
Workbooks.Open FileName:=Datei2
ActiveSheet.Range( "A1:M500" ). Select
Range( "A1:M500" ).Sort Key1:=Range( "A1" ), Order1:=xlAscending, Header:=xlNo
Selection.Replace " " , ""
Selection.Replace "." , ","
|
...also eher so....
Option Explicit
Sub Vergleichen()
Dim Datei1 As String, Datei2 As String
Dim wb1 As Workbook, wb2 As Workbook
Dim lastrow As Long, i As Long, j As Long
Dim writerow As Long, erg As Boolean
Dim Spaltenzahl As String
Datei1 = Application.GetOpenFilename()
If Datei1 = "Falsch" Then Exit Sub
Datei2 = Application.GetOpenFilename()
If Datei2 = "Falsch" Then Exit Sub
Application.ScreenUpdating = False
Spaltenzahl = InputBox("Wie viele Spalten (vertikal) sollen verglichen werden? (Programm startet bei Spalte A.)")
Set wb1 = Workbooks.Open(Datei1)
Set wb2 = Workbooks.Open(Datei2)
Workbooks.Open Filename:=Datei1
ActiveSheet.Range("A1:M500").Select
With wb1.ActiveSheet
.Range("A1:M500").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo
.Replace " ", ""
.Replace ".", ","
End With
With wb2.ActiveSheet
.Range("A1:M500").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo
.Replace " ", ""
.Replace ".", ","
End With
For i = 1 To Spaltenzahl 'letzte zu überprüfende Zeile in Spalte 1-3 bestimmen
lastrow = WorksheetFunction.Max(wb1.ActiveSheet.Cells(Rows.Count, i).End(xlUp).Row)
lastrow = WorksheetFunction.Max(wb2.ActiveSheet.Cells(Rows.Count, i).End(xlUp).Row)
Next i
'...
Set wb1 = Nothing
Set wb2 = Nothing
End Sub
...und lies Dir das mal durch....
https://www.online-excel.de/excel/singsel_vba.php?f=78
https://www.online-excel.de/excel/singsel_vba.php?f=61
Gruß,
|