Methode default range fehlgeschlagen

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Methode default range fehlgeschlagen
von: Stefan
Geschrieben am: 15.10.2015 09:10:34

Hallo an alle,
ich bin neu im VBA programmieren.
In der Ausbildung hatte ich die Programmierung von Siemens Logo und S7, deshalb kann ich mich zumindest ein wenig eindenken.
Alles was mit VBA zu tun hat habe ich aus einem Einsteigerbuch, Foren und dem Makro-Rekorder.

Jetzt hänge ich aber fest und komme nicht ohne direkte Hilfe weiter.

Ich schreibe für mein Ehrenamt einen Stundenzettel. Diesen Zettel gibt es als Datei für jeden Ehrenamtlichen, in dieser Datei ist für jeden Monat ein extra Tabellenblatt.
Unterschieden wird bei der Abrechnung noch um es sich um Nacht- oder Tagesbetreuung handelt und ob diese in Wolfen oder Bitterfeld (2 Arbeitsorte) stattfand.

Jetzt möchte ich für nächstes Jahr eine Datei erstellen, die eine Übersicht gibt, welcher Ehrenamtliche in welchem Monat wie viel Stunden am Tag bzw. in der Nacht geleistet hat.
Eine Tabelle in dieser Datei für Bitterfeld, eine für Wolfen und eine gesamte.

Jetzt zur Theorie.
Mein Grundgedanke der VBA hatte diese Struktur:
- Wenn ein Stundenzettel gespeichert wird, dieses Makro starten
- Name des Ehrenamtlichen in eine Variable speichern
- Stunden BTF, Stunden WO, Nächte BTF und Nächte WO pro Monat in ein Array speichern
- Die Datei „Budget“ öffnen
- Den Namen des Ehrenamtlichen suchen
- Ist er nicht vorhanden, neue Zeile an alphabetisch richtiger Stelle erstellen
- Auf den einzelnen Tabellenblättern anhand des Arrays alles eintragen
- Speichern ( Budget ) , ohne zu fragen
- Schließen( Budget ) , ohne zu fragen

So, und voller Stolz kann ich berichten, dass es funktioniert. AUßER: wenn ich einen Stundenzettel neu öffne und zum ersten mal Speichere, dann bringt er mir „Die Methode „_Default“ für das Objekt ‚Range‘ ist fehlgeschlagen“.
Beende oder Debugge ich an dieser Stelle und speichere ein zweites mal, läuft das Programm ganz sauber durch und funktioniert von der ersten bis zur letzten Zeile. (Die Datei Budget habe ich zwischendurch wieder ohne zu speichern von Hand geschlossen, also hat sie die selbe Ausgangslage)


Jetzt die wichtigen Codezeilen

Dim gesamtName As String
Dim Uebertrag(0 To 12, 1 To 4) As Single
Dim ZeileName As Range
Dim ZeileName2 As Integer

For i = 1 To 12

Uebertrag(i - 1, 1) = Sheets(1 + i).Range("T47") 'Stunden Wolfen
Uebertrag(i - 1, 2) = Sheets(1 + i).Range("T48") 'Stunden Bitterfeld
Uebertrag(i - 1, 3) = Sheets(1 + i).Range("V47") 'Nächte Wolfen
Uebertrag(i - 1, 4) = Sheets(1 + i).Range("V48") 'Nächte Bitterfeld
Next i


Workbooks("Budget Ehrenamtliche.xlsm").Sheets(1).Activate ' Wolfen auswählen

Set ZeileName = Columns(1).Find(what:=gesamtName, Lookat:=xlWhole)
ZeileName2 = ZeileName.Row

For l = 1 To 12

Cells(ZeileName2 + 0, l + 3) = Uebertrag(l, 1) 'Stunden Wolfen (In dieser Zeile kommt der Fehler)
Cells(ZeileName2 + 1, l + 3) = Uebertrag(l, 3) 'Nächte Wolfen
Cells(ZeileName2 + 2, l + 3) = Uebertrag(l, 1) + Uebertrag(l, 3) 'Gesamt Wolfen

Next l



Habt ihr eine Idee wo das Problem liegt?

Bild

Betrifft: AW: Methode default range fehlgeschlagen
von: fcs
Geschrieben am: 15.10.2015 10:40:46
Hallo Stefan,
es scheint ja für die Funktion des Makros eine Information erforderlich zu sein, die in einer noch nicht/nie gespeicherten neuen Datei fehlt (z.B. Dateiverzeichnis, Dateiname) oder falsch ist.
Hast du schon mal bei einer neuen Datei in dem Makro vor der Problemzeile im VBA-Editor einen Haltepunkt gesetzt? Dann kannst du das Makro mit Taste F8 schrittweise weiter ausführen und die Werte der Variablen kontrollieren.
In deiner Problemzeile könnte ZeileName2 den Wert 0 haben, obwohl dann ein Fehler schon vorher bei ZeileName.Row auftreten müsste.
Gruß
Franz

Bild

Betrifft: AW: Methode default range fehlgeschlagen
von: Stefan
Geschrieben am: 15.10.2015 13:13:39
Und wenn ich mir das jetzt genau anschaue, wird es immer absurder.
Also ich öffne die Datei. Dann speichere ich, um das Makro zu starten. Wenn es einen Fehler hatte beende ich das Makro und schließe beide Tabellen ohne erneut zu speichern.
Unter diesen Bedingungen, die in jedem test also gleich sind, hängt er völlig ohne Grund mal bei l=2, l=5 oder l=6. Und es gibt auch den Fall, dass er nicht hängt.
Mit Anwendung des Haltepunktes kam kein Fehler.
Das soll einer verstehen

Bild

Betrifft: AW: Methode default range fehlgeschlagen
von: fcs
Geschrieben am: 15.10.2015 16:34:13
Hallo Stefan,
die Tatsache, das es mal funktioniert und mal nicht, im gemütlichen Schrittmodus aber immer, ist ein Anzeichen, dass irgendein Vorgang abläuft oder eben nicht, bevor das Makro die nächste Aktion ausführen will.
Versuche mal das Makro in die nachfolgende Struktur zu bringen, dann sind die Objekte konkreter zugewiesen und es sind keine Activate/Select-Aktionen vorhanden.
Gruß
Franz


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call prcData_to_Budget
End Sub
Sub prcData_to_Budget()
    Dim wkbBudget As Workbook
    Dim wkbFH As Workbook
    Dim wksMonat As Worksheet
    Dim wksBudget As Worksheet
    
    Dim gesamtName As String
    Dim Uebertrag(0 To 12, 1 To 4) As Single
    Dim ZeileName As Range
    Dim ZeileName2 As Integer
    Dim i As Long, l As Long
    
    On Error GoTo Fehler
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Set wkbFH = ThisWorkbook 'Diese Datei mit den Daten je Monat des freiwilligen Helfers
    'Daten in Variablen einlesen
    With wkbFH
        gesamtName = .Sheets("????").Range("????")
        For i = 1 To 12
            Set wksMonat = wkbFH.Sheets(1 + i)
            With wksMonat
                Uebertrag(i - 1, 1) = .Range("T47") 'Stunden Wolfen
                Uebertrag(i - 1, 2) = .Range("T48") 'Stunden Bitterfeld
                Uebertrag(i - 1, 3) = .Range("V47") 'Nächte Wolfen
                Uebertrag(i - 1, 4) = .Range("V48") 'Nächte Bitterfeld
            End With
        Next i
    End With
    
    'Budget-Datei ggf. öffnen
    If fncWorkbookOpen("Budget Ehrenamtliche.xlsm") = True Then
        Set wkbBudget = Application.Workbooks("Budget Ehrenamtliche.xlsm")
    Else
        Set wkbBudget = Application.Workbooks.Open(Filename:=ThisWorkbook.Path & Application. _
PathSeparator _
            & "Budget Ehrenamtliche.xlsm") 'Pfad ggf. anpassen
    End If
    'Wolfen mit Daten füllen
    Set wksBudget = wkbBudget.Sheets(1) ' Wolfen auswählen
    With wksBudget
        Set ZeileName = .Columns(1).Find(what:=gesamtName, LookIn:=xlValues, Lookat:=xlWhole)
        If ZeileName Is Nothing Then
            MsgBox "Leider """ & gesamtName & """ in Blatt """ & wksBudget.Name & """ nicht  _
gefunden!", _
                vbOKOnly, "Suchen ""gesamteName"""
            GoTo Beenden
        Else
            ZeileName2 = ZeileName.Row
            For l = 1 To 12
                .Cells(ZeileName2 + 0, l + 3).Value = Uebertrag(l - 1, 1) 'Stunden Wolfen
                .Cells(ZeileName2 + 1, l + 3).Value = Uebertrag(l - 1, 3) 'Nächte Wolfen
                .Cells(ZeileName2 + 2, l + 3).Value = Uebertrag(l - 1, 1) + Uebertrag(l - 1, 3)  _
'Gesamt Wolfen
            Next l
        End If
    End With
    
    
    'Bitterfeld mit Daten füllen
    Set wksBudget = wkbBudget.Sheets(2) ' Bitterfeld auswählen
    With wksBudget
        Set ZeileName = .Columns(1).Find(what:=gesamtName, LookIn:=xlValues, Lookat:=xlWhole)
        If ZeileName Is Nothing Then
            MsgBox "Leider """ & gesamtName & """ in Blatt """ & wksBudget.Name & """ nicht  _
gefunden!", _
                vbOKOnly, "Suchen ""gesamteName"""
        Else
            ZeileName2 = ZeileName.Row
            For l = 1 To 12
                'bei Uebertrag muss der Zeilen-Index l-1 sein, sonst werden Daten von _
                        Dezember nicht korrekt eingetragen
                .Cells(ZeileName2 + 0, l + 3) = Uebertrag(l - 1, 2) 'Stunden Bitterfeld
                .Cells(ZeileName2 + 1, l + 3) = Uebertrag(l - 1, 4) 'Nächte Bitterfeld
                .Cells(ZeileName2 + 2, l + 3) = Uebertrag(l - 1, 2) + Uebertrag(l - 1, 4) ' _
Gesamt Bitterfeld
            Next l
        End If
    End With
    
    wkbBudget.Close savechanges:=True
    Set wkbBudget = Nothing
Beenden:
Fehler:
    With Err
        Select Case .Number
            Case 0 'alles OK
            Case Else
                MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description
                If Not wkbBudget Is Nothing Then wkbBudget.Close savechanges:=False
        End Select
    End With
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub
Public Function fncWorkbookOpen(ByVal strName As String) As Boolean
    'prüft, ob Arbeitsmappe mit Name schon geöffnet
    Dim wkb As Workbook
    On Error GoTo Fehler
    Set wkb = Application.Workbooks(strName)
Fehler:
    fncWorkbookOpen = Not wkb Is Nothing
End Function


Bild

Betrifft: AW: Methode default range fehlgeschlagen
von: Stefan
Geschrieben am: 16.10.2015 10:46:42
So. Vielen Dank für deine mühen ich weiß das absolut zu schätzen. Aber ich glaube ich habe mich mit meinem wissen wahnsinnig überschätzt und werde dich bitten müssen den gesamten code vor Fehlern zu sichern.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
 
Dim fVerz As Object
Dim Aufwstund As Integer
Dim Aufwnacht As Integer
Dim Uebertrag(0 To 12, 1 To 4) As Single
Dim Vorname As String
Dim Nachname As String
Dim gesamtName As String
Dim Zeile As Integer
Dim j As Integer
Dim ZeileName As Range
Dim ZeileName2 As Integer
 
 
 
 
 
Vorname = Sheets(1).Range("B1").Value
Nachname = Sheets(1).Range("B2").Value
 
gesamtName = Nachname & ", " & Vorname
 
'MsgBox gesamtName
 
Aufwstund = Sheets(1).Range("B5")
Aufwnacht = Sheets(1).Range("B6")
   
    
    For i = 1 To 12
       
        Uebertrag(i - 1, 1) = Sheets(1 + i).Range("T47")      'Stunden Wolfen
        Uebertrag(i - 1, 2) = Sheets(1 + i).Range("T48")      'Stunden Bitterfeld
        Uebertrag(i - 1, 3) = Sheets(1 + i).Range("V47")      'Nächte Wolfen
        Uebertrag(i - 1, 4) = Sheets(1 + i).Range("V48")      'Nächte Bitterfeld
'MsgBox uebertrag(i, 1) & i
 
 
Next i
 
'MsgBox Uebertrag(1, 1)
'
'   Daten ausgeben
'
 
Workbooks.Open ThisWorkbook.Path & "\Budget Ehrenamtliche.xlsm"
 
 
 
 
 
If Range("a5:a1000").Find(gesamtName) Is Nothing Then       ' Namen suchen
         'MsgBox "nicht gefunden   " & gesamtName
      j = 6
   
    Do Until (StrComp(gesamtName, Cells(j, 1).Value, vbTextCompare) = -1) Or Cells(j, 1).Value = _
 ""   'alphabetisch suchen
  
        j = j + 3
        If j = 6000 Then Exit Do
   
    Loop
         
      
        For k = 1 To 3                                      ' auf 3 Tabellenblättern neu  _
erstellen und sortieren
   
    
        Workbooks("Budget Ehrenamtliche.xlsm").Sheets(k).Activate
   
        Range("A3:R5").Select
        Selection.Copy
        Rows(j).Select
        Selection.Insert Shift:=xlDown
        Application.CutCopyMode = False
        Cells(j, 1).Value = gesamtName
        Range("A1").Select
       
        Next k
   
End If
 
  
  
  
 
 
' Namen suchen und füllen
 
 
Workbooks("Budget Ehrenamtliche.xlsm").Sheets(1).Activate ' Wolfen auswählen
 
    Set ZeileName = Columns(1).Find(what:=gesamtName, Lookat:=xlWhole)
    ZeileName2 = ZeileName.Row
 
        For l = 1 To 12
 
        Cells(ZeileName2 + 0, l + 3) = Uebertrag(l, 1)                   'Stunden Wolfen
        Cells(ZeileName2 + 1, l + 3) = Uebertrag(l, 3)                   'Nächte Wolfen
        Cells(ZeileName2 + 2, l + 3) = Uebertrag(l, 1) + Uebertrag(l, 3) 'Gesamt Wolfen
 
        Next l
 
 
Workbooks("Budget Ehrenamtliche.xlsm").Sheets(2).Activate ' Bitterfeld auswählen
 
    Set ZeileName = Columns(1).Find(what:=gesamtName, Lookat:=xlWhole)
    ZeileName2 = ZeileName.Row
 
        For m = 1 To 12
 
        Cells(ZeileName2 + 0, m + 3).Value = Uebertrag(m, 2)                   'Stunden  _
Bitterfeld
        Cells(ZeileName2 + 1, m + 3).Value = Uebertrag(m, 4)                   'Nächte  _
Bitterfeld
        Cells(ZeileName2 + 2, m + 3).Value = Uebertrag(m, 2) + Uebertrag(m, 4) 'Gesamt  _
Bitterfeld
       
        Next m
 
 
Workbooks("Budget Ehrenamtliche.xlsm").Sheets(3).Activate ' Gesamt auswählen
 
    Set ZeileName = Columns(1).Find(what:=gesamtName, Lookat:=xlWhole)
    ZeileName2 = ZeileName.Row
 
        For n = 1 To 12
 
        Cells(ZeileName2 + 0, n + 3).Value = Uebertrag(n, 1) + Uebertrag(n, 2)                   _
'Stunden Gesamt
        Cells(ZeileName2 + 1, n + 3).Value = Uebertrag(n, 3) + Uebertrag(n, 4)                   _
'Nächte Gesamt
        Cells(ZeileName2 + 2, n + 3).Value = _
        Uebertrag(n, 1) + Uebertrag(n, 2) + Uebertrag(n, 3) + Uebertrag(n, 4)            ' _
Gesamt Wolfen
        Next n
       
       ' speichern und schließen
       
Workbooks("Budget Ehrenamtliche.xlsm").Close savechanges:=True
       
        
        
 
End Sub
 
 
Private Sub Workbook_Open()
Sheets(1).Range("B1").Value = Sheets(1).Range("B1").Value
Sheets(1).Range("B2").Value = Sheets(1).Range("B2").Value
End Sub
Ich glaube von mir stecken gefühlte 10 Stunden Arbeit drin.sieht man das denn wenigstens?
Da ich die nächsten Codes besser schreiben möchte wäre es auch nett wenn du ein wenig beschreibst warum bestimmte Zeilen dazu gehören.
Jetzt noch die Beschreibung für den unteren Sub.
Mit Hilfe einer anderen Datei stehen alle Namen. Anhand dieser Namen kopiere ich für jeden Namen die Stunden Datei und benenne sie mit Namen. Diesen Namen aus dem Pfad lass ich über normale Excel Funktionen anzeigen und dann beim ersten öffnen von vba fest schreiben. Sicherlich eine doofe Idee. ..

Bild

Betrifft: AW: Methode default range fehlgeschlagen
von: fcs
Geschrieben am: 16.10.2015 21:33:49
Hallo Stefan,
ich hab jetzt mal versucht, das ganze in eine Form umzusetzen, wie sie weniger anfällig gegen Probleme sein sollte.
Ich hätte es an deiner Stelle aber anders gelöst.
Ich hätte die erstellten Dateien der Helfer mit den Stundendaten in einer Schleife abgearbeitet (Datei schreibgeschützt öffnen, Daten auslesen (ähnlich wie jetzt in ein Array), Datei wieder schliessen, Daten aus Array in Budget-Datei schreiben, ähnlich wie jetzt auch.
Das Makro wäre dann in der Budget-Datei angelegt, statt in der AfterSave-Prozedur der Dateien mit den Monatsdateien.
In deinem Makro hab ich folgende Prinzipien umgesetzt:
1. alle verwendeten Variablen werden in Dim-Anweisungen deklariert
Dies Erleichter beim Programmieren die Erstellung von Code, da die Eingabe eines Punktes nach der Variablen die Auswahlliste der gültigen Eigenschaften/Methoden anzeigt (Optionen müssen ggf. im VBA-Editor entsprechend eingestellt werden.)
2. die 1. Zeile im Modul ist
Option Explicit
Dies erzwingt das Deklarieren der Variablen und reduziert Fehler bei Schreibweisen der Variablen.
3. praktisch kompletter Verzicht auf Activate und Select/Selection durch verwendung von Objekt-Variablen für Arbeitsmappen und Tabellenblätter.
4. Verwendung von With ... End With Konstrukten
Dies erspart die Wiederholung von Objekt-Namen vor Cells/Range. Gilt analog für alle anderen Methoden/Eigenschaften.
5. Recycling von Variablen.
Du musst nicht für jede For-Next-Schleife eine andere Variable für den Schleifenzähler verwenden.
Dies ist nur erforderlich, wenn die Schleifen geschachtelt sind.
Diesen Namen aus dem Pfad lass ich über normale Excel Funktionen anzeigen und dann beim ersten öffnen von vba fest schreiben. Sicherlich eine doofe Idee. ..
Das Problem bei dieser Lösung ist, dass du dir die Vorlage-Datei zerschießt, wenn du beim Öffnen der Vorlage die Makros nicht deaktivierst. Hier könntest du nach dem Kopieren der Vorlagen, diese in einer Schleife kurz öffnen und die Namen fest reinschreiben. Dann ist das Workbook_Open Makro nicht erforderlich.
Gruß
Franz
Text-Datei mit Code
https://www.herber.de/bbs/user/100840.txt

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Methode default range fehlgeschlagen"