Clever-Excel-Forum

Seiten: 1 2

Hallo liebe Gemeinde,

ich weiß gerade nicht so wirklich, wo der Fehler liegt und bitte Euch um Unterstützung.

Ich habe ein Excelfile mit mehreren Arbeitsblättern. Auf dem Hauptarbeitsblatt mit dem Namen "Feldgeräteliste" sind alle Daten einer großen Lüftungsanlage aufgelistet. Wenn ich jetzt die Daten der Volumenstromregler aus dieser Feldgeräteliste ausdrucken möchte, habe ich ein separates Arbeitsblatt mit einem speziellen Layout. Mit einem VBA-Script suche ich nach Einträgen, welche mit "VVR" gekennzeichnet sind. VVR steht für variable Volumenstromregler. Das Script hat schon super funktioniert, aber dann habe ich auf der Zieltabelle "Print_VSR" eine Spalte gelöscht. Den VBA-Code habe ich angepasst, jedoch genau an der Löschstelle kann ich die Spalte nicht mehr adressieren.
Die Zeile mit Spalte 5/Raumbezeichnung/Montageort habe ich auskommentiert, da diese Spalte im Ausdruck der Zieltabelle nicht mehr benötigt wird. Jedoch wird der Versorgungsbereich Spalte 7 nicht dorthin geschrieben, sondern in die Spalte 8. Es sieht so aus, dass die Spaltennummern in dieser Tabelle sich durch die Löschung einer Spalte nicht angepasst haben. Ich habe aber geprüft mit =Spalte() - hier werden mir jedoch die aktuellen geänderten Spaltennummern angezeigt. Nur im VBA funktioniert das nicht, er adressiert nicht die richtigen Spalten hinter der Löschstelle. Füge ich wieder eine Spalte ein, stimmt alles.
Wo ist mein Gedankenfehler?

Vielen Dank für Eure Ideen,
Candalfo

Code:
            ' Kopiere die relevanten Zellen von der Quellzeile zur Zieltabelle

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 1).Value = Zeile.Cells(1, 1).Text ' Anlagen BAS

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 2).Value = Zeile.Cells(1, 2).Text ' Anlagenname

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 3).Value = Zeile.Cells(1, 6).Text ' Feldgeräte BAS

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 4).Value = Zeile.Cells(1, 8).Text ' Etage

            'ZielBereich.ListRows(ZielZeile).Range.Cells(1, 5).Value = Zeile.Cells(1, 9).Text ' Raumbezeichnung/Montageort

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 5).Value = Zeile.Cells(1, 10).Text ' Raumnummer

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 6).Value = Zeile.Cells(1, 11).Text ' Bemerkung

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 7).Value = Zeile.Cells(1, 19).Text ' Versorgungsbereich

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 8).Value = Zeile.Cells(1, 20).Text ' ZUL/ABL

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 9).Value = Zeile.Cells(1, 21).Text ' AUF/ZU

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 10).Value = Zeile.Cells(1, 22).Text ' 0%-100%

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 11).Value = Zeile.Cells(1, 23).Text ' 0-10V

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 12).Value = Zeile.Cells(1, 24).Text ' 24V

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 13).Value = Zeile.Cells(1, 25).Text ' Nenn-Volumenstrom

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 14).Value = Zeile.Cells(1, 26).Text ' Sollwert-Volumenstrom 1

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 15).Value = Zeile.Cells(1, 27).Text ' Stellsignal-Volumenstrom 1

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 16).Value = Zeile.Cells(1, 28).Text ' Sollwert-Volumenstrom 2

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 17).Value = Zeile.Cells(1, 29).Text ' Stellsignal-Volumenstrom 2

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 18).Value = Zeile.Cells(1, 30).Text ' Aufschaltung Rückmeldung 0-10V

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 19).Value = Zeile.Cells(1, 31).Text ' VSR-Regler Typ-Bezeichnung

Hallo

ich habe so eine Programmierung noch nie gesehen, weiss nicht ob das so okay ist???
'Zielbereich.ListRows(Zielzeile).Range.Cells(1, 1).Value = "aa" 'Zeile.Cells(1, 1).Text

Was soll ListRows(Zielzeile) bewirken?? ListRows ist für mich die gesamtzahl an Zeilen, und darin ein Index??
Range kann man Cells auch weglassen, das verwirrt nur. Der ganze Code ist mir noch schleierhaft.
Kannst du uns den Zielbereich vom Range her noch mal genauer erklären.

mfg Gast 123

Hallo Gast,

vielleicht wird es transparenter, wenn ich den gesamten Code einfüge.
Wie gesagt, der Code funktioniert ja. Aber als ich auf der Zieltabelle eine Spalte gelöscht habe und ich hier im Kopiervorgang auch die entsprechende Spalte löschte und die nachfolgenden angepasst habe, werden die Daten ab der Stelle, wo die Spalte gelöscht wurde, in die falsche Spalte geschrieben.

ZielZeile ist die Variable, die die Zielzeilennummer enthält, in die die Daten kopiert werden sollen.

Code:
Sub DatenSammelnUndKopieren()

    ' Zeige eine Messagebox mit dem Hinweis an

    Dim msgBoxResult As VbMsgBoxResult

    msgBoxResult = MsgBox("Bitte warten Sie, die Daten werden gesammelt!", vbInformation, "Daten werden gesammelt")

    ' Überprüfe, ob der Benutzer auf "Abbrechen" geklickt hat

    If msgBoxResult = vbCancel Then Exit Sub

    ' Führe das bestehende Skript aus

    DatenKopierenInTabelleVSR

    ' Informiere den Benutzer über den Abschluss des Kopiervorgangs

    MsgBox "Der Kopiervorgang wurde abgeschlossen!", vbInformation, "Abgeschlossen"

End Sub

Sub DatenKopierenInTabelleVSR()

    Dim QuellBereich As Range

    Dim ZielZeile As Integer

    Dim Zieltabelle As Worksheet

    Dim ZielBereich As ListObject

    Dim Zeile As Range ' Deklariere die Variable Zeile

    ' Definiere die Quelltabelle und den Bereich

    With Worksheets("Feldgeräteliste").ListObjects("TAB_Feldgeräteliste")

        Set QuellBereich = .DataBodyRange

    End With

    ' Definiere die Zieltabelle

    Set Zieltabelle = Worksheets("Print_VSR")

    ' Definiere den Zielbereich in der Zieltabelle

    Set ZielBereich = Zieltabelle.ListObjects("Druck_VSR")

    ' Durchlaufe jede Zeile im Quellbereich

    For Each Zeile In QuellBereich.Rows

        ' Überprüfe das Merkmal "VVR" und kopiere die relevanten Zellen, wenn es den Kriterien entspricht

        If Zeile.Cells(1, 4).Value = "VVR" Then

            ' Inkrementiere die Zielzeilennummer für den nächsten Datensatz

            ZielZeile = ZielZeile + 1

            ' Überprüfe, ob die Zielzeile über die Anzahl der Zeilen der Zieltabelle hinausgeht

            If ZielZeile > ZielBereich.ListRows.Count Then

                ' Füge eine neue Zeile am Ende der Tabelle hinzu, wenn nötig

                ZielBereich.ListRows.Add

            End If

            ' Kopiere die relevanten Zellen von der Quellzeile zur Zieltabelle

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 1).Value = Zeile.Cells(1, 1).Text ' Anlagen BAS

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 2).Value = Zeile.Cells(1, 2).Text ' Anlagenname

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 3).Value = Zeile.Cells(1, 6).Text ' Feldgeräte BAS

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 4).Value = Zeile.Cells(1, 8).Text ' Etage

            'ZielBereich.ListRows(ZielZeile).Range.Cells(1, 5).Value = Zeile.Cells(1, 9).Text ' Raumbezeichnung/Montageort

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 5).Value = Zeile.Cells(1, 10).Text ' Raumnummer

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 6).Value = Zeile.Cells(1, 11).Text ' Bemerkung

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 7).Value = Zeile.Cells(1, 19).Text ' Versorgungsbereich

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 8).Value = Zeile.Cells(1, 20).Text ' ZUL/ABL

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 9).Value = Zeile.Cells(1, 21).Text ' AUF/ZU

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 10).Value = Zeile.Cells(1, 22).Text ' 0%-100%

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 11).Value = Zeile.Cells(1, 23).Text ' 0-10V

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 12).Value = Zeile.Cells(1, 24).Text ' 24V

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 13).Value = Zeile.Cells(1, 25).Text ' Nenn-Volumenstrom

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 14).Value = Zeile.Cells(1, 26).Text ' Sollwert-Volumenstrom 1

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 15).Value = Zeile.Cells(1, 27).Text ' Stellsignal-Volumenstrom 1

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 16).Value = Zeile.Cells(1, 28).Text ' Sollwert-Volumenstrom 2

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 17).Value = Zeile.Cells(1, 29).Text ' Stellsignal-Volumenstrom 2

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 18).Value = Zeile.Cells(1, 30).Text ' Aufschaltung Rückmeldung 0-10V

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 19).Value = Zeile.Cells(1, 31).Text ' VSR-Regler Typ-Bezeichnung

            ' ... (setze den Code für die anderen Spalten fort)

            ' Setze das Zahlenformat für die relevanten Spalten in der Zieltabelle auf "Text"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 3).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 6).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 11).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 12).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 13).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 14).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 15).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 16).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 17).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 18).NumberFormat = "@"

            ZielBereich.ListRows(ZielZeile).Range.Cells(1, 19).NumberFormat = "@"

            ' ... (setze den Code für die anderen Spalten fort)

        End If

    Next Zeile

End Sub

Hallo zusammen,

habe den Fehler gefunden...das Problem saß mal wieder 50cm vor dem Bildschirm. Ich hatte vergessen, dass ich im Quellbereich auch Spalten gelöscht habe und die Indizes sich hier auch geändert haben.

Hilfreich war bei der Fehlersuche dieser Code, um die Indizes in jeder Tabelle anzuzeigen:

Code:
Sub SpaltenIndizesAnzeigen()

    Dim i As Integer

    For i = 1 To Columns.Count

        Cells(1, i).Value = i

    Next i

End Sub

Viele Grüße
Candalfo.

Hi,

einige Anmerkungen...

ZielBereich.ListRows(ZielZeile).Range.Cells(1, 1) hier ist das .Range überflüssig. es reicht ZielBereich.ListRows(ZielZeile).Cells(1, 1) wobei eigentlich sogar schon ZielBereich.Cells(ZielZeile, 1) reichen würde.

Dein Code würde wesentlich schneller laufen, wenn du nicht jede Zelle einzeln kopieren würdest. Sondern zusammenhängende Blöcke gemeinsam. Jeder Zellzugriff kostet enorm Zeit. Wobei der Zugriff auf eine Zelle praktisch gleich lang dauert wie der gleichzeitige Zugriff auf 100.000 Zellen. Im Augenblick machst du 2x19 Zellzugriffe. Da du vier Blöcke hast, könnte man das auf 2x4 Zellzugriffe reduzieren und dein Programm bräuchte nur noch ca. 20% der bisherigen Zeit.
Allerdings wird das nicht mit der Eigenschaft .Text sondern nur mit .Value funktionieren.

Hallo Helmut,

vielen Dank für Deine Tipps zur Optimierung meines Codes. In der Tat dauert eine gefüllte Tabelle schon ein wenig. Da ich aber noch in den VBA-Beginner-Schuhen stecke, sind manche meiner Wege sicherlich umständlich. Leider treibt mich das Fertigstellungsdatum, die Optimierung zu testen werde ich, wenn das Projekt erstmal grundsätzlich läuft.
Die Sache mit dem Text, warum ich es so lösen musste. In der Quellzelle stehen Werte wie 02.123, was für die Raumnummer gilt. Im Ziel kam es dann zu Ergebnissen wie 02123, obwohl auch die Zielzelle als Text formatiert wurde. Ich habe noch nicht ganz verstanden, wie Excel mit den Formaten bei solchen Aktionen umgeht. Vielleicht hast Du einen Tipp für mich?

Viele Grüße
Candalfo

Hi Gandolfo,

wenn du einer Zelle einen Wert zuweist, indem du .Value oder auch z.B. .Formula verwendest, dann versucht Excel die "Eingabe" zu interpretieren. So kann es dann vorkommen, dass 2.103 als die Zahl 2103 interpretiert und auch so behandelt wird.

Eine Möglichkeit dies zu verhindern ist tatsächlich die von dir verwendete Methode - mit dem Nachteil, dass hinterher in der Zelle auch wirklich ein Text steht. Bei einem Datum wie 01.02.2024 (was der Zahl 45323 entspricht) hast du im Anschluss in der Zielzelle eben kein Datum mehr stehen, sondern nur einen Text, der wie ein Datum aussieht. Wenn du dann einen Doppelklick in die Zelle machst und Enter drückst, hast du plötzlich doch wieder ein Datum. Alles nicht schön.

Eine bessere Möglichkeit ist es, nicht den Wert sondern die Zelle (sprich Wert plus Formate) zu kopieren. Das geht dann so:

Code:
Range("A1:A10").Copy Range("B1:B10")

Danach hast du in B1:B10 eine Kopie von A1:A10 - mit allen Formaten und ohne dass Excel versucht die "Eingabe" zu interpretieren. Damit ist dies übrigens noch schneller als Range("B1:B10").Value = Range("A1:A10").Value

Wenn man keine Formeln übertragen will (was Range("A1:A10").Copy Range("B1:B10") macht), verwendet man

Code:
Range("A1:A10").Copy

Range("B1:B10").PasteSpecial xlPasteValuesAndNumberFormats    'oder xlPasteValues oder ein anderes der xlPaste-Argumente

Auch hier wird nichts interpretiert und es ist rasend schnell.

Moin Helmut,

super, vielen Dank. Das löst ein paar Probleme.
Dir eine gute Restwoche,
Viele Grüße
Candalfo

Moinsen,

ich habe auch noch zwei Anmerkungen:
1. Du musst dir in Listobjects keine Spaltennummern merken. VBA kann anhand der Überschrift die Spalte identifizieren (im folgenden Beispiel wird der Inhalt der Zweiten Zelle in der Spalte Vorname im Direktfenster ausgegeben):

Code:
Debug.Print ActiveSheet.ListObjects(1).ListColumns("Vorname").DataBodyRange.Cells(2)

2. Eine Schleife um nach Werten zu suchen ist in Excel nicht sehr sinnvoll. Nutze lieber den Autofilter. in einer sortierten Liste und kopiere den gesamten sichtbaren Block:

Code:
Sub zwei()

    Dim lo As ListObject

    Set lo = ActiveSheet.ListObjects(1)

    With lo.Sort

        .SortFields.Add lo.ListColumns("Kategorie").Range

        .Apply

        lo.Range.AutoFilter field:=lo.ListColumns("Kategorie").Index, Criteria1:="VVR"

        Intersect(lo.DataBodyRange.Offset(1, 0), lo.DataBodyRange.SpecialCells(xlCellTypeVisible), lo.ListColumns("Vorname").DataBodyRange).Copy Tabelle2.Cells(1, 1)

        lo.AutoFilter.ShowAllData

        .SortFields.Clear

        .SortFields.Add lo.ListColumns("ID").Range

        .Apply

        .SortFields.Clear

    End With

End Sub

Viele Grüße
derHöpp

Moin derHöpp,

vielen Dank. Der von Dir gezeigte Autofilter ist die selbe Funktion wie =Filter() als Formel? Mit dieser Funktion habe ich das Problem, dass ich die nicht benötigten Spalten nicht ausfiltern kann.

Viele Grüße
Candalfo

(01.02.2024, 16:34)Candalfo schrieb: [ -> ]dass ich die nicht benötigten Spalten nicht ausfiltern kann.

Ich verstehe nicht, was das bedeuten soll….

Seiten: 1 2

Candalfo

Gast 123

Candalfo

HKindler

Candalfo

HKindler

Candalfo

derHoepp

Candalfo

HKindler