Excel VBA Unklarheit über Select Fehler

[Allan Sche Sar]

Hallo ihr,

ich sitze gerade an einer VBA Anwendung, die mir zwei Excel Dateien miteinander vergleichen soll und in einer der beiden Dateien Ungleichheiten farbig markieren soll.
Dieses funktioniert auch in meinen kleinen Testdateien super. Nun sollte ich es auf die original zu prüfende Datei anwenden und bekomme den Fehler:

Laufzeitfehler '1004':
Die Select-Methode des Worksheet-Objektes konnte nicht ausgeführt werden.

die entsprechende Stelle ist bei "Dateineu.Worksheet(KM_(k)).Select zu finden (Zeile 9).

Hier nun der Auszug meiner Prüfroutine.

    j = 1
    For k = 1 To kn 'For-Schleife über die Blattanzahl
        For j = 1 To 40 'For-Schleife über die maximale Spaltenbreite
            For i = 1 To 120 'For-Schleife über die maximale Zeilenanzahl
                On Error GoTo sprungmarke2
                If Datei.Worksheets(KM_(k)).Cells(i, j) <> Dateineu.Worksheets(KM_(k)).Cells(i, j) Then
                    'pinkfarbiges markieren der unterschiedlichen Zellen
sprungmarke2:
                    Dateineu.Worksheets(KM_(k)).Select
                    Dateineu.Worksheets(KM_(k)).Cells(i, j).Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 16711935
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End If
            Next i
        Next j
    Next k

Dateineu ist dabei die Excel Datei, die mit dem original überprüft werden soll, während Datei die Originaldatei darstellt.

Könnt ihr mir weiterhelfen? Denn bisher hat der Select-Aufruf immer funktioniert.

 

[_killy_]

Ich bin nur kurz drüber geflogen, aber aus meiner Sicht passt Zeile 9 nicht. In Zeile 10 gibst du nochmal genau an, was markiert werden soll. Da erübrigt sich die Zeile 9.

Ansonsten einfach mal Schritt für Schritt ausführen lassen und sagen, in welcher Zeile / bei welchem Durchlauf der Fehler auftritt.

PS müsste es nicht auch

Dateineu.Worksheets("KM_(k)")

heißen?

 

[ConVuzius]

Soweit ich weiß kennt Worksheet keine Select-Methode.
Ich denke was du suchst ist Worksheet.Activate

Edit: Wie killy eigentlich schon sagt, sollte das ganze auch komplett ohne Zeile 9 funktionieren.

 

[JohnnieWalker]

wird wohl "KM_(" & k & ")" heißen müssen.. ich gehe dabei einfach mal davon aus, dass die blätter KM_1, KM_2, KM_3 heißen..

ansonsten stimmt es, dass zeile 9 im grunde obsolet ist

 

[Allan Sche Sar]

Nein, es muss nicht Dateineu.Worksheets("...")heißen, da KM_(k) eine Variable ist und kein Name.
Der Fehler kommt etwa nach 1/5 aller zu durchsuchender Zellen. Insgesamt gibt es 19.200 Zellen.

Jetzt springt mein Tool einfach über die Bestellung von KM_(k) hinweg. Keine Ahnung warum, aber ich glaube ich muss heute die Arbeit daran einstellen, weil ich nichts mehr sehe - Betriebsblind.
Daher kann ich euch auch jetzt noch nicht sagen, ob der Austausch von Select zu Activate etwas gebracht hat.

Vielleicht werfe ich heute Abend noch mal den Rechner an und versuche den Fehler weiter vorn zu finden.

 

[cumulonimbus8]

Mangels guter Ideen eine schlechte: ist die angepeilte Zelle/Zellkoordinate vielleicht Teil einer verbunden Zelle?
Oder irgendwie gesperrt?
Lass doch mal für den Direktbereich ein Debug.Print für die Indexe mitlaufen und betrachte mal das Objekt der Begierde.

Du bist jedenfalls sicher, dass beim Auftreten des Unfalls die Indexe korrekte Blätter ansprechen; nicht dass du exakt bei einem Schritt zum nächsten Zählindex scheiterst? Da es in Zeile 9 rummst wäre das denkbar.

Ich würde übrigens wo immer es geht auf SELECT (oder das angesprochene ACTIVATE) verzichten.
With Dateineu.Worksheets(KM_(k)).Cells(i, j).Interior
Sollte als Zeile 10 (ohne 9 und 11) grundsätzlich funktionieren.

CN8

 

[Allan Sche Sar]

Die Interior Funktion werde ich morgen einmal überprüfen.
Wo du aber gerade ansprichst, dass die Überprüfung in verbunden Zellen stattfindet. Ja das tut sie. Es gibt in den beiden Excel Dateien verbundene Zellen. Diese sind jedoch in beiden Dateien gleich.
Muss ich dieses gesondert behandeln?

Edit: Mit Interior bin ich weiter gekommen. Nun hänge ich an einer anderen Stelle. Für folgenden, veränderten Code, erhalte ich den Fehler

Laufzeitfehler '13': Typen unverträglich

    j = 1
    For k = 1 To kn 'For-Schleife über die Blattanzahl
        For j = 1 To 40 'For-Schleife über die maximale Spaltenbreite
            For i = 1 To 120 'For-Schleife über die maximale Zeilenanzahl
                On Error GoTo sprungmarke2
                If Datei.Worksheets(KMo_(k)).Cells(i, j) <> Dateineu.Worksheets(KMv_(k)).Cells(i, j) Then
sprungmarke2:
                    'pinkfarbiges markieren der unterschiedlichen Zellen
                    With Dateineu.Worksheets(KMv_(k)).Cells(i, j).Interior
'                    Dateineu.Worksheets(KM_(k)).Select
'                    Dateineu.Worksheets(KM_(k)).Cells(i, j).Select
'                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 16711935
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End If
            Next i
        Next j
    Next k

Der Fehler tritt in Zeile 6 auf. Wenn ich mir die Werte im Direktbereich für die If-Anweisung anzeigen lassen erhalte ich folgende Werte:

?Datei.Worksheets(KMo_(k)).Cells(i, j).Value
Fehler 2007
?Dateineu.Worksheets(KMv_(k)).Cells(i, j).value
34,998

In der Datei steht an der stelle ein #DIV/0! und in Dateineu der gerundete Wert 35 €

Wie kann ich nun diesen Fehler abfangen? Ich habe doch schon extra vor der If-Anweisung eine Error Routine drin.

Ergänzung (19. September 2014)

Okay mittlerweile kann ich durch die erste Excel Datei durchlaufen und diese mit der anderen vergleichen.
Für die Nachwelt hier einmal der Quellcode:

    j = 1
    For k = 1 To kn 'For-Schleife über die Blattanzahl
        For j = 1 To 40 'For-Schleife über die maximale Spaltenbreite
            For i = 1 To 120 'For-Schleife über die maximale Zeilenanzahl
                On Error GoTo sprungmarke2
                If IsError(Datei.Worksheets(KMo_(k)).Cells(i, j)) Then
                    GoTo sprungmarke2
                    ElseIf IsError(Dateineu.Worksheets(KMo_(k)).Cells(i, j)) Then
                    GoTo sprungmarke2
                    ElseIf Datei.Worksheets(KMo_(k)).Cells(i, j) <> Dateineu.Worksheets(KMv_(k)).Cells(i, j) Then
                    
sprungmarke2:
                    'pinkfarbiges markieren der unterschiedlichen Zellen
                    With Dateineu.Worksheets(KMv_(k)).Cells(i, j).Interior
'                    Dateineu.Worksheets(KM_(k)).Select
'                    Dateineu.Worksheets(KM_(k)).Cells(i, j).Select
'                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 16711935
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End If
            Next i
        Next j
    Next k

 

[_killy_]

So mal Optimierungsmaßnahme: erstelle dir eine Hilfsspalte wo du alle Spalten zusammenfasst - dann brauchst du nur noch die zusammengefassten Spalten vergleichen und erst dann bei einem Unterschied kannst du innerhalb dieser Zeile dann die Spalten einzeln vergleichen.
Das Makro sollte dadurch stark beschleunigt werden, wenn du davon ausgehen kannst, dass du eine große Übereinstimmung zwischen den zu vergleichenden Daten hast.

Bsp. Hilfsspalte A
=B1&B2&B...
oder
=verketten(B1;B2;B...)


Weiterhin den Select Befehl entfernen, frei aus der Hand:

With Dateineu.Worksheets(KM_(k)).Cells(i, j).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 16711935
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Bringt auch nochmal eine Geschwindigkeitsverbesserung.

 

[Allan Sche Sar]

he _killy_

die Selectbefehle sind bereits alle entfernt. Diese stehen im letzten Code nur noch auskommentiert dort.
Eine weitere Optimierung (Verketten der Spalten) brauch ich aber derzeit nicht vornehmen, da das Makro nur 35 Sekunden läuft. Außerdem möchte ich die Dateien so wenig wie möglich verändern, denn mit der überprüften Datei muss weiter gearbeitet werden und da die Mappe mit vielen anderen Makros arbeitet, sind die 35 Sekunden legitim für die Ausführung.

Derzeit schreibe ich noch eine Routine mit der ich überprüfe, ob die Tabellenblätter auch die gleichen Namen habe und wenn nicht, soll gesucht werden, ob es an einer anderen Position vorhanden ist.
Denn blöd wäre es, wenn ich die Überprüfung einfach stur durchführe und aber in der veränderten Datei fehlt das zweite von 40 Tabellenblätten.

Ergänzung (19. September 2014)

Ich brauch noch mal eure Hilfe. Und zwar funktionier es in meiner Testdatei, dass während des Prüfvorgangs ein Protokoll auf einem extra Blatt erstellt wird.
Doch in der richtigen Datei sind alle vorhandenen Arbeitsblätte mit einem Blattschutz versehen und ich bekomme den Fehler:

Laufzeitfehler '1004': Die Methode 'Add' für das Objekt 'Sheets' ist fehlgeschalfen

Der entsprechende Quellcode sieht so aus:

'---------------------------------------------------------------------------------------------------
'Überprüfen der korrekten Blattreihenfolge, damit immer gleiche Blätter verglichen werden
'---------------------------------------------------------------------------------------------------
    'einfügen des Protokollblattes in die veränderte Datei
    Dateineu.Sheets.Add After:=Sheets(Sheets.Count)
    Dateineu.ActiveSheet.Name = "Protokoll"
        With ActiveWorkbook.Sheets("Protokoll").Tab
        .Color = 192
        .TintAndShade = 0
    End With
    
   'Kopfzeile anlegen
    ActiveWorkbook.Sheets("Protokoll").Cells(1, 1) = "Blattname original Datei"
    ActiveWorkbook.Sheets("Protokoll").Cells(1, 2) = "Blattname veränderte Datei"
    Columns("A:B").Select
    Columns("A:B").EntireColumn.AutoFit
    Range("A1:B1").Select
    Selection.Font.Bold = True

Zeile 5 wird mir vom Debugger angezeigt. Ich selber kann aber in der Excel Datei ein neues Blatt anlegen.
Wie könnte ich am besten vorgehen?

 

[Allan Sche Sar]

Ich lasse nun alle Daten, die im XLSM Format vorliegen in eine XLSX speichern, doch auch danach erhalte ich den Fehler, dass das neue Tabellenblatt nicht hinzugefügt werden kann.
Wenn ich jedoch manuell vorher die XLSM Dateien in XLSX speichere, bekomme ich keinen Fehler, wenn ich mein Analysemakro durchlaufen lasse.