1.8k Aufrufe
Gefragt in Tabellenkalkulation von

Hallo alle miteinanderwink

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 angry

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 angry wenn ich die Sub Mail() in jedem TB manuell aufrufe dann macht er es frown


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 cheeky. Bin echt schon am verzweifeln devil

Über dringende Hilfe wäre ich sehr dankbar.

LG SnowWhite

75 Antworten

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Bearbeitet von m-o

Hallo,

du sprichst zwar mit der Schleife die einzelnen Tabellenblätter an, aber wie du schon gemerkt hast, greifst du nur das aktuelle Tabellenblatt zu.

Nutze WITH um auf die einzelnen Tabellen zuzugreifen und achte auf den Punkt vor den Cells - und Range-Befehlen (damit werden nämlich die Zellen in dem betreffenden Arbeitsblatt angesprochen):

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
    'hier nun auf die einzelnen Arbeitsblätter zugreifen
    With WS
       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)
    End With
    
    Next WS
End Sub

Natürlich könnstest du statt mit WITH auch jede Zelle so ansprechen:

WS.Cells(i, 7).Value

Mit WITH sparst du dir die Angabe des Tabellenblattes for jeder Cells- oder Range-Anweisung im entsprechenden WITH-Block.

Gruß

M.O.

0 Punkte
Beantwortet von
Bearbeitet

Hallo M.O.

Vielen lieben Dank für deine Rückmeldung.

Ich habe beide Varianten probiert nur irgendwie funktioniert das überhaupt nicht. devil Bin vielleicht doch zu blöd surprise

Falls du eine Mail für mich hättest könnte ich dir die ganze Datei schicken (100% sauber) den er fängt in einer TB an wo er gar nichts zu suchen hat sad

LG

p.S. Weil im Prinzip eigentlich alles funktioniert nur das "für alle Tabellen" -> nicht funktioniert surprise

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo,

du kannst die Datei hier im Forum hochladen: Anleitung

Gruß

M.O.

0 Punkte
Beantwortet von

Hallo

Ich hoffe es klappt so frown Excel

Mail Adresse bei den TB "E-Mail Prof und "E-Mail Assistent" muss zum test noch dazu eingetragen werden^^

GLG

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Bearbeitet von m-o

Hallo,

damit das Makro richtig ausgeführt wird, musst du natürlich bei der Ermittlung der letzten beschriebenen Zeile das Tabellenblatt angeben (siehe meinen oben geposteten bearbeiteten Code). Also auch in den folgenden Zeilen:

Debug.Print CStr(WS.Cells(WS.Rows.Count, 1).End(xlUp).Row) ' ermittelt die erste Zelle ohne Wert (formatierte Zellen bleiben unberücksichtigt)

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

Das selbe Problem mit den Tabellennamen hast du natürlich auch in den Makros, mit denen du die Mails verschickst. Auch hier werden die Mailadressen immer aus dem aktuellen Tabellenblatt gelesen. Hier müsstest du auch den Namen des gerade bearbeiteten Tabellenblatts mit übergeben und die Cells-Anweisungen anpassen.

Gruß

M.O.

0 Punkte
Beantwortet von

Hallo M.O.

Ich bedanke mich sehr herzlich für deine Rückmeldung.
Ich hab jetzt vor den Cells und Rows  das  "WS." stehen aber funktionieren tut nichts crying
Aber ich steh irgendwie komplett am Schlauch gerade.frown

Das selbe Problem mit den Tabellennamen hast du natürlich auch in den Makros, mit denen du die Mails verschickst. Auch hier werden die Mailadressen immer aus dem aktuellen Tabellenblatt gelesen. Hier müsstest du auch den Namen des gerade bearbeiteten Tabellenblatts mit übergeben und die Cells-Anweisungen anpassen.

Wie ? ich muss den Namen des gerade bearbeiteten TB mit übergeben ? Ich verstehs nicht *sorry* 

Wie und wo mache ich denn das ?

LG 

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo SnowWhite,

du rufst z.B. dein Makro zum Senden der Erinnerung mit dem Befehl:

Call Send_Erinnerung(i)

Mit dem i in den Klammern übergibst du die Zeile (siehe das betreffende Makro)

Private Sub Send_Erinnerung(ByVal Zeile As Long)

Die übergebene Zeile weist du der Variable iRow zu und liest damit Daten für deine Mail aus:

 iRow = Zeile

..

sEmail_Anrede = Cells(iRow, 9)

Aber die Daten werden aus dem gerade aktiven Tabellenblatt gelesen und nicht aus dem Blatt, das du mit deiner Schleife gerade aufrufst (wie du ja sicher bei den Mails festgestellt hast). Also müsstest du z.B. den Namen des Tabellenblatts genau wie die Zeilennummer beim Aufruf der Mail-Makros mit übergeben und die Cells-Anweisungen in diesen Makros anpassen, damit die Daten für die E-Mails aus den richtigen Tabellenblättern eingelesen werden.

Ich werde die Makros mal anpassen und mich dann wieder melden.

Gruß

M.O.

0 Punkte
Beantwortet von

Hallo M.O. 

Ja genau aus der TB "urliste" werden 1x die neuen Tabellenblätter geniert. Dann soll er im Prinzip nach der Daten Eingabe von den MA ab der Sub Mail() in jedem neuen Tabellenblatt ->  jede Zeile durch gehen und wenn er das TB durch gegangen ist dann das nächste neu generiere TB usw.... Beim durch kompilieren ist mir schon aufgefallen das er irgendwie hin und her hupft in den TB´s und nicht die TB´s nach der Reihe macht frown

Aber da ich noch nie VBA Excel programmiert habe sondern eigentlich nur VB bzw VBA Word winkprogrammiere bin ich mit den Zellen, Spalten, Blätter, Mappe .....  irgendwie komplett überfordert cheeky 

Vor allem weil er nicht in den neu generierten Blätter anfängt sondern in den ersten 5 Tabellenblätter anfängt wo er gar nichts zu suchen hat frown 

Ich wäre über deine Hilfe und Anpassung wirklich sehr dankbar blush

GLG SnowWhite

p.S. das heißt ich müsste die Variable " WS aus der Sub Mail () in die anderen Subs Send_Erinnerung(i) und Send_Email(i) mit übernehmen ?

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo SnowWhite,

ich sehe, du hast noch mehr Probleme als nur die Namen der Tabellenblätter. winkIch hatte mich schon gewundert, warum alle Blätter durchlaufen werden, aber die Daten - nach meinem Verständnis - ja nur in einigen Blättern stehen.

Habe ich das richtig verstanden, dass das Makro nur die Tabellenblätter druchlaufen soll, die du über die Tabelle "urliste" anlegst? Heißen diese Tabellen immer Liste 1 etc? Oder falls sich die Namen ändern können, bleiben die Namen in Tabellenblatt "urliste" in Spalte A dauerhaft stehen?

Zur Übergabe des Namens:

Den Aufruf kannst du z.B. so machen:

Call Send_Email(i, WS.Name)

Voraussetzung ist natürlich, dass das dies im aufzurufenden Code auch deklariert ist:

Private Sub Send_Email(ByVal Zeile As Long, WsName As String)

Dann kannst du die Daten entsprechend einlesen:

With ThisWorkbook.Worksheets(WsName)
            
            sEmail_Anrede = .Cells(iRow, 9)
            sEmail_Adresse = .Cells(iRow, 11)
            sEmail_Ausgangsdatum = .Cells(iRow, 6)
            sEmail_Bezeichnung = .Cells(iRow, 2)
            sEmail_Liegenschaft = .Cells(iRow, 15)
            
    End With

Gruß

M.O.

0 Punkte
Beantwortet von

Guten Abend M.O. wink

Momentan bestehe ich nur noch aus "Problemen" blush mit der verdammten VBA Excel Datei ^^

Habe ich das richtig verstanden, dass das Makro nur die Tabellenblätter druchlaufen soll, die du über die Tabelle "urliste" anlegst?

Ja genau das ist richtig blush

Heißen diese Tabellen immer Liste 1 etc? Oder falls sich die Namen ändern können, bleiben die Namen in Tabellenblatt "urliste" in Spalte A dauerhaft stehen?

Die Namen ändern sich "Liste1" usw ist jetzt nur ein Test-Text (für mich)  blush die Namen legen dann die Mitarbeiter fest aus denen dann die TB generiert werden! Aber JA die Namen bleiben im TB "urliste" (hoffe der Name "urliste" wird nicht geändert cheeky) und auf alle Fälle "fix"  in der "Spalte A" dauerhaft.

Genau Ziel ist: Die Datei wird von einer Mitarbeiterin 1 x am Tag aufgerufen dann soll sich quasi über die "Form" mit "JA" bestätigen und dann die Tabellenblätter im jetzigen Testfall "Liste 1, Liste 2, Liste 3 ....." ab der Sub Mail()  der ganze Code in den  TB´s  -> jetztige TB´s ( "Liste 1, Liste 2, Liste 3 .....") durchlaufen werden.  Und dann eben die Mails mit den Daten aus der z.B. TB "Liste 1" weg schicken an die jeweiligen Personen.  (hoffe das war jetzt nicht chinesisch) blush

Sorry, Sorry, Sorry ich habe jetzt komplett den Faden verloren devil. Was soll ich jetzt ändern  blush?

Hättest du vielleicht deine geänderte Version für mich wink
Dann würdest du mich echt retten blush

Man ehrlich das bleibt meine erste und letzte VBA Excel Datei crying

GLG SnowWhite

...