Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Zelleninhalte als Tabellenname definieren - Teil 2

Betrifft: Zelleninhalte als Tabellenname definieren - Teil 2 von: D_Gonzalez
Geschrieben am: 28.07.2014 14:21:01

Hallo Zusammen,

ich habe hier im Forum vorher schonmal eine Frage zu einem Makro gestellt. Da diese Makro jedoch mehrere Dinge tut und ich nur eine Frage zu einer Aufgabe habe, hier nochmal die gleich Frage etwas verdichtet:

Ich habe eine Excel-Datei mit den Tabellenblättern "Daten", "Werte" und mehreren Tabellenblätter mit Zahlenbezeichnungen, nehmen wir für das Beispiel "111", "222", "333" und "444" an. In diesen vier letzgenannten Tabellenblättern gibt es einen Datenbereich zwischen ("A12:D12") über mehrere Zeilen hinweg. Also einmal beispielsweise ("A12:D14") (Ab Zeile 15 ist die Tabelle leer), ein anderes Mal ("A12:D17") (ab Zeile 18 ist die Tabelle leer) usw..

In Tabellenblatt "Werte" soll in Spalte A eine Auswahl aus 3 der 4 Tabellenblätter mit Zahlenbezeichnung gewählt werden, indem die Bezeichungen dieser Tabellenblätter eingetragen werden (Beispiel: A1 = "111", A2 = "222" und A3 = "444").

Mein Makro sieht folgendermaßen aus:

Sub Makro_Test()

Dim i As Integer
Dim j As Integer

For i = 1 To 3
    j = Cells(i, 1).Name
    Worksheets(j).Select

    Range("A12:D12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Worksheets("Werte").Range("A1000").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste

Next i

End Sub
Im ersten Durchlauf der Schleife sollte j den Wert "111" einnehmen, dann sollte im Tabellenblatt "111" der definierte Wertebereich kopiert werden usw.
Im zweiten Durchlauf sollte j dann "222" sein und mithilfe des Makros der definierte Wertebereich aus Tabellenblatt "222" kopiert werden usw.

Ich habe offensichtlich Probleme mit der Definition von j. Integer könnte falsch sein, Dim As Worksheet funktioniert aber leider auch nicht.
j = Cells(i, 1).Name ist wahrscheinlich ganz falsch, ich weiss aber nicht, wie es eigentlich sein sollte.

Ich hoffe hier kann mir jemand weiterhelfen, mein Latein ist am Ende.

Vielen Dank und vielen Grüße
D_Gonzalez

  

Betrifft: .Value statt .Name owT von: Rudi Maintaire
Geschrieben am: 28.07.2014 14:50:07




  

Betrifft: AW: .Value statt .Name owT von: D_Gonzalez
Geschrieben am: 28.07.2014 14:58:01

Hallo Rudi,

das funktioniert, ich bleibe dann allerdings in der Zeile Worksheets(j).Select hängen. Laufzeitfehler 9, Index außerhalb des gültigen Bereichs. Das Makro speichert den Wert als j, auf Worksheet(j) kann aber nicht zugegriffen werden.

Viele Grüße


  

Betrifft: AW: .Value statt .Name owT von: EtoPHG
Geschrieben am: 28.07.2014 15:00:50

Hallo D & Ruedi,

Das kann so und anders nicht funktionieren, weil
Der Registername zwar ein Nummer sein kann, aber nur vom Datentyp String.
Mit .Value übergibst du einen Integer und damit meint VBA, es handle sich um einen Blattindex und nicht einen Blattnamen!
Für mich hat es aber noch mehr Ungereimtheiten, und v.a. völlig überflüssige .Select

so, vielleicht:

Sub Makro_Test()

    Dim lRow As Long
    
    For lRow = 1 To 3
        ' Name der 3 Blätter wird aus Zelle A1:A3 gelesen
        On Error Resume Next
        With Worksheets(Worksheets("Werte").Cells(lRow, 1).Text)
            Select Case Err.Number
            Case 0
                ' Kopiert wird der Bereich aus A12:Dx
                ' nach "Daten" Spalte 1 kleinste leere Zeile von unten
                .Range(.Cells(12, 1), .Cells(.Rows.Count, 4).End(xlUp)).Copy _
                Destination:=Worksheets("Daten").Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
            Case 9
                MsgBox "Es gibt kein Tabellenblatt mit Namen [" & _
                        Worksheets("Werte").Cells(lRow, 1).Text & "]", vbCritical, "falscher  _
Blattname!"
            Case Else
                MsgBox Err.Description, vbCritical, "Fehler: " & Err.Number
            End Select
            Err.Clear
        End With
    Next lRow
End Sub
Gruess Hansueli


  

Betrifft: eingebürgert? von: Rudi Maintaire
Geschrieben am: 28.07.2014 15:16:42

Hallo,
Ruedi ist eindeutig schweizerisch ;-)
Mit Dim j As String sollte es funktionieren.

Gruß
Rudi


  

Betrifft: werden Steuerflüchtlinge automatisch ;-) (owT) von: EtoPHG
Geschrieben am: 28.07.2014 15:47:39




  

Betrifft: Steuerflüchtling von: Rudi Maintaire
Geschrieben am: 28.07.2014 22:47:00

Hallo,
1. ist mein Einkommen nicht hoch genug um zu flüchten.
2. zahle ich gerne viel Steuern, weil ich dann auch viel verdiene.
3. sind SF in meinen Augen asoziales Gesocks, die einerseits alle Vorteile eines gut organisierten Gemeinwesens nutzen aber andererseits nicht dafür zahlen wollen.

Gruß
Rudi


  

Betrifft: AW: eingebürgert? von: D_Gonzalez
Geschrieben am: 28.07.2014 16:57:24

Hallo Hansueli,

vielen Dank für deine Hilfe, ich versuche dein Makro anzupassen und entsprechend zu verwenden. Leider ohne Erfolg. Was ich nicht brauche ist die Massagebox, die mir sagt, das es für einen Wert kein entsprechend äquivalentes Tabellenblatt gibt. Wie gesagt, das Problem ist hier verkürzt dargestellt. Es gibt bereits eine andere Absicherung.

Was ich aber gerne nutzen würde ist folgendes:

.Range(.Cells(12, 1), .Cells(.Rows.Count, 4).End(xlUp)).Copy _
Destination:=Worksheets("Daten").Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)

Der VBA-Editor ist damit aber nicht zufrieden. "Fehler beim Kompilieren; Unzulässiger oder nicht definierter Verweis" ist die Fehlermeldung.

Mein Makro sieht mittlerweile so aus:

Sub Makro_Test()

Dim i As Integer
Dim j As String
        
Set Auswertung = Workbooks("FTE_Aggregation.xlsm").Worksheets("Werte")
    
    For i = 1 To 3
        j = Cells(i, 1).Value
        Worksheets(j).Select
    
        .Range(.Cells(12, 1), .Cells(.Rows.Count, 4).End(xlUp)).Copy _
        Destination:=Worksheets("Daten").Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
   
    
        'Range("A12:D12").Select
        'Range(Selection, Selection.End(xlDown)).Select
        'Selection.Copy
        'Worksheets("Werte").Cells(Rows.Count, 1).End(xlDown).Offset(1, 0).Select
        'ActiveSheet.Paste
    
    Next i


End Sub
Ich bin mir sicher, ich vermische in unzulässiger Art und Weise deinen Code und meinen Code. Ich habe auch schon ein paar andere Dinge versucht, die ich jetzt nicht alle hierrein kopieren will, das Ergebnis bleibt aber immer das gleiche: Laufzeitfehler XY


  

Betrifft: AW: eingebürgert? von: EtoPHG
Geschrieben am: 29.07.2014 08:42:56

Hallo D?,

Diesen Befehl kannst du nur innerhalb eines With-Konstrukts nutzen, das einem Worksheet-Objekt zugeordnet ist. Ich weiss nicht, warum du bei deinem VBA-Level versuchst 2 komplett verschiedene Ansätze zu vermischen. Vor allem solltes du auf .Select/.Activate verzichten, denn das sind zu 99.9% fehleranfällige, überflüssige Methoden in VBA. Lies dazu zuerst mal diesen Artikel und versuche dann deine Code-Konstrukte gem. den Vorschlägen anzupassen.

Gruess Hansueli


  

Betrifft: AW: eingebürgert? von: D_Gonzalez
Geschrieben am: 29.07.2014 10:57:14

Hallo Hansueli,

wahrscheinlich habe ich mich beim VBA-Level noch überschätzt, trotzdem vielen Dank für deine (eure) Geduld. Ich habe mich jetzt für den With-Ansatz entschieden, es klappt aber leider immernoch nicht wie erwünscht:

Sub Makro_Test()

Dim i As Long
         
    For i = 1 To 3
        With Worksheets(Worksheets("Daten").Cells(i, 1).Text)
                
            .Range(.Cells(12, 1), .Cells(.Rows.Count, 4).End(xlUp)).Copy _
            Destination:=Worksheets("Werte").Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)

        End With
        
    Next i

End Sub
Das ist dein Code entsprechend gekürzt. Ich komme den gewünschten Ergebnis langsam näher, bin aber noch nicht ganz da.

Das Makro überprüft beim Kopiervorgang fälschlicherweise die 4. Spalte nach Werten, was aber für den Zweck nicht zeilführned ist, da dort immer eine Formel steht. Kopiert werden soll der Bereich A12:Dx, wenn in Spalte Ax+1 kein Wert mehr ist. Folgendes editiertes Stückchen aufgezeichnetes Makrocode hat die Aufgabe in dem Ansatz ohne With richtig gelöst.

Range("A12:D12").Select
Range(Selection, Selection.End(xlDown)).Copy

Ich kann aber keinen Unterschied erkennen, der erklären würde warum bei diesem Ansatz die Spalte A analysiert wird, in oben dargestelltem Markro aber Spalte D. Hat jemand eine Idee?

Viele Grüße
D_Gonzalez


  

Betrifft: AW: eingebürgert? von: EtoPHG
Geschrieben am: 29.07.2014 11:08:48

Hallo D,

dann korrigiere/ergänze:

.Range(.Cells(12, 1), .Cells(.Rows.Count, 1).End(xlUp).Offset(0,3)).Copy _
            Destination:=Worksheets("Werte").Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)

Gruess Hansueli


  

Betrifft: AW: eingebürgert? von: D_Gonzalez
Geschrieben am: 29.07.2014 11:22:14

Hallo Hansueli,

es funktioniert. Juhuuu!

Vielen Dank!