Makro für die gesamten Tabellen in der Arbeitsmappe.! Wo ist denn der Fehler ?

Question

Hallo alle miteinander

Ich bin hier im Forum eh auch schon alle Beiträge die irgendwie mit meinen Problem zusammen hängen durch gegangen und getestet aber ich finde den Fehler einfach nicht

Kurzerklärung. Die Datei wird geöffnet -> mit UserForm1.Show -> mit "Ja" bestätigt und mit Sub Mail() soll er den ganzen Code auf jedes TB ausführen. Nur irgendwie macht er das nicht er macht es immer nur für das akutelle TB  wenn ich die Sub Mail() in jedem TB manuell aufrufe dann macht er es 

---

Private Sub Workbook_Open()
UserForm1.Show
End Sub

---

Private Sub CommandButton1_Click()
Application.Run "Mail"
End Sub

---

Private Sub Mail()

Dim Zeile As Long
Zeile = 1
Check (Zeile)  'Sprung in den Aufruf Check()

End Sub

Private Sub Check(ByVal Zeile As Long)
    Dim WS As Worksheet
    Dim Urgenz1 As String
    Dim Urgenz2 As String
    Urgenz1 = "x"
    Urgenz2 = "x"
    Dim i As Long
    
    For Each WS In ThisWorkbook.Worksheets 'alle Tabellenblätter durchlaufen
    
    Const xlUp As Long = &HFFFFEFBE
    Debug.Print CStr(Cells(Rows.Count, 1).End(xlUp).Row) 
      
    For i = Zeile + 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If (CDate(Cells(i, 7).Value) < DateTime.Date) And (CStr(Cells(i, 13).Value) = "x") Then ' Wenn Spalte "G" Datum = heutiges Datum & Spalte "M" auf "Leer" Dann
        'MsgBox "Email schon geschickt", vbInformation, "Fertig" 'Message Box als Hilfe stellung

        ElseIf (CDate(Cells(i, 7).Value) < DateTime.Date) And (CStr(Cells(i, 13).Value) = vbNullString) Then 'Wenn Spalte "G" Datum = heutiges Datum & Spalte "M" auf "Leer" Dann
            Cells(i, 13).Value = Urgenz1 'Füge Urgenz1 in Spalte M
            Call Send_Email(i)  'Aufruf Prozedur "Send_Email()"
        End If
        
        If (CDate(Cells(i, 8).Value) < DateTime.Date) And (CStr(Cells(i, 14).Value) = vbNullString) Then 'Wenn Spalte "H" Datum = heutiges Datum & Spalte "N" auf "Leer" Dann
            Cells(i, 14).Value = Urgenz2 'Füge Urgenz2 in Spalte N
            Call Send_Erinnerung(i)  'Aufruf Prozedur "Send_Erinnerung" 
        End If
    Next i
'    Call MsgBox("Fertig!", vbOKOnly)
    Next WS
End Sub

---

Kann mir bitte wer sagen was ich falsch mache und wo der verdammte Fehler ist . Bin echt schon am verzweifeln 

Über dringende Hilfe wäre ich sehr dankbar.

LG SnowWhite

Answer 1

Hallo M.O.

Hiiilllfffffeeeee  Was mache ich denn schon wieder falsch ?
A) ich weiß vom "sauberen Programmieren" bin ich bei der Datei momentan weit weg aber er macht mir die Sub auch durch  (auser du hast einen Tipp wie ich es anderes machen kann)

B) er hängt aber bei der Sub Check in der 1.If -Schleife. mit dem Laufzeitfehler '13:' Typen unverträglich

Kurze Info was die schon wieder neues haben wollen: 
Eine MA soll in den TB in der Spalte C "Löschen" jetzt ein "X" machen und er soll dann beim erneuten öffnen der Datei die Zelleninhalte aus den Spalten D, F, I, L, M, und N in der jeweiligen zeit wo das "x" in der Spalte C drinnen ist rauslöschen und dann danach die Sub Mail durchlaufen.

Private Sub Schlüssel_Daten_löschen()
    Dim Zeile As Long
    Zeile = 1
    Dim Löschen As String
    Löschen = "x"
    Dim i As Long
    Dim t As Long
    Dim arrTabellen As Variant
    Dim lngLetzte As Long
    
    With Worksheets("urliste")
            arrTabellen = .Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1))
    End With
    

    For t = LBound(arrTabellen) To UBound(arrTabellen)
      With ThisWorkbook.Worksheets(arrTabellen(t, 1))
    
        For i = Zeile + 1 To .Cells(Rows.Count, 1).End(xlUp).Row
            If (CStr(.Cells(i, 3).Value) = "x") Then
                .Cells(i, 4).ClearContents
                .Cells(i, 6).ClearContents
                .Cells(i, 9).ClearContents
                .Cells(i, 12).ClearContents
                .Cells(i, 13).ClearContents
                .Cells(i, 14).ClearContents
            End If
        Next i
       End With
     Next t
Mail
End Sub

GLG SnowWhite

Answer 2

Hallo SnowWhite,

durch das Löschen werden ja u.a. auch die Urgenzdaten aus den Spalten G und H gelöscht. Und wenn du versuchst eine leere Zelle in ein Datum umzuwandeln, dann kommt der Fehler in deinem Check-Makro.

Deshalb solltest du erst prüfen, ob die Zellen, die ein ggf. ein Datum enthalten nicht leer sind:

Private Sub Check(ByVal Zeile As Long)
    Dim Urgenz1 As String
    Dim Urgenz2 As String
    Urgenz1 = "x"
    Urgenz2 = "x"
    Dim i As Long
    Dim t As Long
    Dim arrTabellen As Variant
    Dim lngLetzte As Long
    
        
    'Namen der neu angelegten Tabellenblätter aus Tabelle urliste einlesen
    With Worksheets("urliste")
            arrTabellen = .Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1))
    End With
    
    'nun werden die neu angelegten Tabellenblätter durchlaufen
    For t = LBound(arrTabellen) To UBound(arrTabellen)
      With ThisWorkbook.Worksheets(arrTabellen(t, 1))
    
        For i = Zeile + 1 To .Cells(Rows.Count, 1).End(xlUp).Row
           If .Cells(i, 7).Value <> "" Then
            If (CDate(.Cells(i, 7).Value) < DateTime.Date) And (.Cells(i, 13).Value = "x") Then ' Wenn Spalte "G" Datum = heutiges Datum & Spalte "M" auf "Leer" Dann
               'MsgBox "Email schon geschickt", vbInformation, "Fertig" 'Message Box als Hilfe stellung

                ElseIf .Cells(i, 7).Value <> "" And (CDate(.Cells(i, 7).Value) < DateTime.Date) And (CStr(.Cells(i, 13).Value) = vbNullString) Then 'Wenn Spalte "G" Datum = heutiges Datum & Spalte "M" auf "Leer" Dann
                   .Cells(i, 13).Value = Urgenz1 'Füge Urgenz1 in Spalte M
                   Call Send_Email(i, arrTabellen(t, 1)) 'Aufruf Prozedur "Send_Email()"
            End If
           End If
           If .Cells(i, 8).Value <> "" Then
             If .Cells(i, 8).Value <> "" And (CDate(.Cells(i, 8).Value) < DateTime.Date) And (CStr(.Cells(i, 14).Value) = vbNullString) Then 'Wenn Spalte "H" Datum = heutiges Datum & Spalte "N" auf "Leer" Dann
               .Cells(i, 14).Value = Urgenz2 'Füge Urgenz2 in Spalte N
               Call Send_Erinnerung(i, arrTabellen(t, 1))  'Aufruf Prozedur "Send_Erinnerung"
             End If
           End If
        Next i
        
       End With
     Next t
        
'    Call MsgBox("Fertig!", vbOKOnly)
    
End Sub

Gruß

M.O.

Answer 3

Guten Morgen M.O.

Das ist mir in der Nacht auch noch eingefallen das er ja quasi die Zellen auf Datentyp und Datum überprüft und da aber gar nichts mehr drinnen steht das es deswegen vielleicht gemeckert hat . Und ob ich da nicht auch noch eine 4 Schleife brauch  

Vielen Lieben Dank 

Eine Frage hat das einen Grund warum du mit <> ""  und nicht mit der Konstante "vbNullString" arbeitest ?

Bei dem anderen Punkt hab ich leider leider noch gar keinen Ansatz wie ich das überhaupt umsetzten könnte.  Muss auch erst schauen ob ich eine "ordentliche Formulierung hin bekomme " 

GLG 

Answer 4

Hallo SnowWhite,

zum sauberen Programmieren:

Schreibe deinen Code und dimensioniere die Variablen erst dann (am besten direkt am Anfang) wenn du sie auch wirklich brauchst.

In dem von mir geänderten Code oben hast du z.B. die folgenden Variablen deklariert, die du hier aber gar nicht (oder noch nicht?) brauchst:

Dim Urgenz1 As String
Dim Urgenz2 As String
Urgenz1 = "x"
Urgenz2 = "x"

Und falls du doch einmal z.B. die x abfragen willst, dann geht das auch einfach direkt ohne irgendwelche Variablen. Das selbe gilt übrigens für deinen Löschen-Code. Bei mir würde er so aussehen:

Private Sub Schlüssel_Daten_löschen()
Dim i As Long
Dim t As Long
Dim arrTabellen As Variant
        
With Worksheets("urliste")
     arrTabellen = .Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1))
End With

For t = LBound(arrTabellen) To UBound(arrTabellen)
   With ThisWorkbook.Worksheets(arrTabellen(t, 1))
    
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            If LCase(.Cells(i, 3).Value) = "x" Then         'mit LCase wird der Inhalt der Zelle in Kleinschrift dargestellt
                .Cells(i, 4).ClearContents
                .Cells(i, 6).ClearContents
                .Cells(i, 9).ClearContents
                .Cells(i, 12).ClearContents
                .Cells(i, 13).ClearContents
                .Cells(i, 14).ClearContents
            End If
        Next i
       End With
     Next t
Call Mail
End Sub

Warum weist du u.a. der Variablen Zeile die 1 zu, beginnst aber den Durchlauf mit

For i = Zeile + 1 To .Cells(Rows.Count, 1).End(xlUp).Row 

Halte den Code so einfach wie möglich.

Eine Frage hat das einen Grund warum du mit <> ""  und nicht mit der Konstante "vbNullString" arbeitest ?

Es bringt beides das selbe Ergebnis, und jeder hat so seinen Stil. Und da ich mir alles selbst beigebracht habe,ist das eben mein Stil.

Gruß

M.O.

Answer 5

Hallo M.O.

Vorab vielen Dank für dein Feedback.Nur am Rande: Da ich seit 10 Jahren aus der Programmierung (reines VB, c++, php...) heraus bin und mir das aufs Auge gedrückt worden ist, musst ich irgendwo ansetzten "als test" -> weil ich gehofft habe das ich sagen kann "Nein Leute das funktioniert nicht" , was sich ja leider jetzt als Irrtum heraus gestellt hat.

Bzgl
In dem von mir geänderten Code oben hast du z.B. die folgenden Variablen deklariert, die du hier aber gar nicht (oder noch nicht?) brauchst:
 

Ich verwende doch die beiden Variablen 

Dim Urgenz1 As String
Dim Urgenz2 As String
Urgenz1 = "x"
Urgenz2 = "x

in "Sub Check" in den "IF-Schleifen". Oder bin ich jetzt Blind 
Wie ich mir die Programmierung beigebracht habe, stand immer in den Büchern "zuerst Variable als Typ definieren und dann der Variable die Werte zuweisen." Damit man mit den "Variablen" arbeitet statt mit den "Werten direkt  Und das ist bei mir irgendwie hängen geblieben 

Das selbe gilt übrigens für deinen Löschen-Code. Bei mir würde er so aussehen:

Das ist natürlich was anderes als wie bei mir da stimme ich dir auch voll und ganz zu  So sahen meine Codes auch in aktiven meiner Programmierzeit auch mal aus. Ehrlich gesagt es ist da soviel hin und her geändert worden das ich jetzt nicht wüsste wie ich den wieder "sauber" bekomme 
Aber wenn du mit "LCase" den Zelleninhalt in Kleinschrift darstellst, wandelt der es auch automatisch um wenn die MA ein Großes "X" eingeben?  Bei mir gibts so einige "Speziallisten" in der Firma was das angeht 

Warum weist du u.a. der Variablen Zeile die 1 zu, beginnst aber den Durchlauf mit

For i = Zeile + 1 To .Cells(Rows.Count, 1).End(xlUp).Row 

Gute Frage ! Warum hab ich das gemacht? Antwort kann ich dir gerade nicht geben -> Ich weiß nicht mehr warum.  Weil ständig Änderungen gekommen sind wo ich gerade mit einem Teil fertig war.

Eine Frage hat das einen Grund warum du mit <> ""  und nicht mit der Konstante "vbNullString" arbeitest ?

Es bringt beides das selbe Ergebnis, und jeder hat so seinen Stil. Und da ich mir alles selbst beigebracht habe,ist das eben mein Stil.

Ups ich dürfte jetzt glaub ich ins "Fettnäpchen" getreten sein 
Ich wollte dich jetzt nicht persönlich angreifen -> "Einmal Entschuldigen" 
Mich hat es nur interessiert ob das einen speziellen Grund hat, das du  mit <> ""  arbeitest! Das beide Varianten das selbe machen das ist mir klar 

Bitte nicht böse auf mich sein  bin ja froh das ich dich gefunden habe der mir jetzt noch hilft 

GLG SnowWhite

P.s. Bzgl dem anderen Punk bin ich noch an der Formulierung dran 

Answer 6

Hallo SnowWhite,

Aber wenn du mit "LCase" den Zelleninhalt in Kleinschrift darstellst, wandelt der es auch automatisch um wenn die MA ein Großes "X" eingeben?  Bei mir gibts so einige "Speziallisten" in der Firma was das angeht

Genau das ist die Itention. So ist es egal, ob ein kleines oder großes X in der Zelle steht.

Ups ich dürfte jetzt glaub ich ins "Fettnäpchen" getreten sei

Ich habe das keineswegs irgendwie negativ aufgefasst. Wenn du mal hier in die Tabellenkalkution-Gruppe klickst und die Codes von verschiedenen Helfern anschaust, wirst du sehen, dass jeder den Code etwas anders schreibst. Und deine Frage ist ja duchaus berechtigt.

In dem von mir geänderten Code oben hast du z.B. die folgenden Variablen deklariert, die du hier aber gar nicht (oder noch nicht?) brauchst:

Ich verwende doch die beiden Variablen 

Stimmt, hatte ich nicht gesehen .

Und mach dir keinen Kopf, wenn der Code so für dich Okay ist und er funktioniert, dann ist es doch gut. Das mit den Änderungen kenne ich. Da passiert schon mal, dass es etwas chaotisch wird (und auf einmal nichts mehr funktioniert ).

Gruß

M.O.

Answer 7

Hallo M.O.

Genau das ist die Itention. So ist es egal, ob ein kleines oder großes X in der Zelle steht.

Ok dann habe ich das eh richtig verstanden  ich habe jetzt deine "Sub Schlüssel_Daten_löschen()" genommen!

In dem von mir geänderten Code oben hast du z.B. die folgenden Variablen deklariert, die du hier aber gar nicht (oder noch nicht?) brauchst:

Ich verwende doch die beiden Variablen 

Stimmt, hatte ich nicht gesehen .

Kein Thema  Naja weil die Sub Check Anfangs von mir war bis auf den Punkt wo du jetzt mit eingestiegen bist bzgl der "Worksheets" Funktion.  

Und mach dir keinen Kopf, wenn der Code so für dich Okay ist und er funktioniert, dann ist es doch gut.

Ja er funktioniert - OK ? -> das ist ein anderes Thema wenn´s "sauberer" auch gehen würde, hätte ich nichts dagegen 

Ich habe das keineswegs irgendwie negativ aufgefasst. Wenn du mal hier in die Tabellenkalkution-Gruppe klickst und die Codes von verschiedenen Helfern anschaust, wirst du sehen, dass jeder den Code etwas anders schreibst. Und deine Frage ist ja duchaus berechtigt

Na dann bin ich ja beruhigt  ich bin dann glaub ich doch mehr eher der "Variablen Typ" vor allem bei größeren Projekte wo ich Variablen Übergabe drinnen habe.

Das mit den Änderungen kenne ich. Da passiert schon mal, dass es etwas chaotisch wird (und auf einmal nichts mehr funktioniert ).

Das ist bei uns "täglich"  Ich kenne mich schon langsam durch die ganzen Änderungen nicht mehr aus aber es stimmt schon die Sub Mail() macht ja im Prinzip nichts mehr 

Ahm ich hab dir mal den Letzten Punkt hoffentlich gut & Verständlich zusammengefasst 
Es handelt sich hier noch um das TB „urliste“ und den einzelnen generierten Tabellenblätter

Im TB „urliste“ soll jetzt quasi in der Spalte „A“
z.b. „Liste 1“, „Liste 2 “, „Liste 3 “ usw…

Und in der Spalte „B“
z. B „Wien“, „Graz“, „Salzburg“ usw…

Also das sie dann so aussieht z.B.

urliste

Liste 1	Wien
Liste 2	Salzburg
Liste 3	Graz

Nur ich habe da absolut keinen blassen Ansatzschimmer wie ich das umsetzten soll  
der Punkt ist:  in den „Neuen“ generierten Tabellenblättern soll in der Spalte „O“ in jeder Zelle der Text aus der Spalte „B“ stehen aus dem TB „urliste“

Das es noch in die Sub "Anlegen" gehört = 
 A) ist mir klar 
 B) nur das es nicht mein Code ist, sondern den habe ich im Internet gefunden 
 C) und der mir irgendwie immer noch Kopfzerbrechen bereitet  

(deswegen meine Comments) hinter meine Codes ->damit ich noch auskenne!

Da ich wahrscheinlich weiß was dann noch kommen wird (wenn das mal steht ) wäre noch die Frage ob das ginge ?

Frage: Nach dem generieren der TB´s , das er im TB „urliste“ die Inhalte aus der Spalte „A“ und Spalte „B“ raus löschen soll. 

Wenn das ganze "Modul" umgeschrieben werden muss -> dann fange ich an zu "schreien"  
Ich brauche Hillffeeee 

Ganz, ganz liebe Grüße
SnowWhite

p.S. gerade mein Gedanke noch gekommen: was ist wenn denen "einfällt: Spalten plötzlich raus zu nehmen" Dann gibts doch keine "Spalte O" mehr ?

Answer 8

Hallo SnowWhite,

den Namen kannst du z.B. so einfügen:

Sub Anlegen()
Dim Wiederholungen As Long
Dim wksL As Worksheet
Set wksL = Worksheets("urliste")
Application.ScreenUpdating = False
Worksheets("urliste").Activate

'For Wiederholungen = 1 To Range("A65536").End(xlUp).Row 'Ursprung
For Wiederholungen = 1 To Cells(Rows.Count, 1).End(xlUp).Row

If Cells(Wiederholungen, 1) <> vbNullString Then
    Worksheets("Leer").Copy After:=Sheets(Sheets.Count) 'Tabellenblatt "Leer" wird kopiert
    With ActiveSheet
      'Neues Blatt umbenennen
      .Name = wksL.Cells(Wiederholungen, 1).Text 'Als TB Name des neu erstellten TB wird der Name aus der aktiven Zelle genommen von TB urliste.
      .Range("02:O50") = wksL.Cells(Wiederholungen, 2).Value
    End With
Else
Exit Sub
End If
Worksheets("urliste").Activate
Next

Set wksL = Nothing

Application.ScreenUpdating = True
End Sub

Natürlich kannst du nach dem Anlegen auch die Spalten A und B löschen, dann musst du aber auch das Check-Makro ändern, da hier die Namen der neu angelegten Tabellenblätter aus dem Tabellenblatt "urliste" genutzt werden, um diese zu durchlaufen. Die Spalte B könnte man ohne Probleme löschen. Vielleicht reicht es auch aus, die Schriftfarbe in Spalte A in Weiß zu ändern oder das ganze Tabellenblatt auszublenden, da es ja nur für das Anlegen der neuen Tabellen genutzt wird.

Sollte der Aufbau der Tabellen geändert werden, so sind ggf. natürlich auch die entsprechenden Makros anzupassen. Das solltest du mal ganz klar kommunizieren.

Gruß

M.O.

Answer 9

Guten Morgen M.O.

Vielen Dank für deine Rückmeldung. Also wäre ich jetzt mit meiner Logik wieder mal komplett am Holzweg gelandet :-(

 .Range("02:O50") = wksL.Cells(Wiederholungen, 2).Value

Wenn ich das richtig interpretiere wählst du im jeweiligen TB mittel ".Range("02:O50")" die Zeile 2 bis 50 aus ? Und weißt Ihnen die Inhalte vom TB "urliste" aus der Spalte B zu ?

Aber er hängt da in der 

 .Range("02:O50") = wksL.Cells(Wiederholungen, 2).Value

Mit der Meldung "Laufzeitfehler 1004: "Anwendung- oder objektdefinierter Fehler"

Aber wo definierst du das er die Werte aus der Spalte B in die Spalte O übernehmen soll ?

GLG  SnowWhite

P.S Jetzt trifft genau das ein was ich vermutet habe Spalte D, E und J haben sie gelöscht angeblich brauchen Sie diese nicht mehr  Ich hab gleich gesagt das es hinterher nicht mehr möglich ist ;-) die wieder herzustellen und Änderungen sich auf den Code auswirkt :-( 

Answer 10

Hallo SnowWhite,

sorry hatte das nicht getest . Da hatte ich bei O2 statt des O eine Null getippt. Deshalb funktioniert das nicht. So funktioniert es:

.Range("02:O50") = wksL.Cells(Wiederholungen, 2).Value

Aber wo definierst du das er die Werte aus der Spalte B in die Spalte O übernehmen soll ?

Ich kopiere nur den Inhalt aus der Spalte B der Tabelle "urliste" und zwar mit dem Teil nach dem Gleichheitszeichen:

wksL.Cells(Wiederholungen, 2).Value

Du kannst ja per Code auf den Inhalt aller Zellen in jeder Tabelle zugreifen (auslesen, in andere Zellen kopieren etc; deshalb auch meine Verwunderung mit deinen Variablen Urgenz -siehe oben).

Erstelle einfach mal eine neue Tabelle mit zwei Tabellenblättern, die Tabelle1 und Tabelle2 heißen. Dann schreibst du irgendwas in Tabelle1 in die Zelle A1. Danach führst du den folgenden Code aus:

Sub kopieren()

Worksheets("Tabelle2").Range("A1") = Worksheets("Tabelle1").Range("A1").Value

End Sub

Dann schau mal, was in Tabelle2 in Zelle A1 steht. Das ist genau das selbe Prinzip, wie ich es für das Kopieren des Städtenamens in das neue Tabellenblatt nutze.

Gruß

M.O.

P.S. Ich kann leider nur sagen: Kopf hoch. Vielleicht könntest du ja eine Erschwerniszulage verlangen.