VBA Fehler in Spaltennummer

[Candalfo]

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

[Gast 123]

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

[Candalfo]

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.

[HKindler]

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.

[Candalfo]

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

[HKindler]

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.

[Candalfo]

Moin Helmut,

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

[derHoepp]

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

[Candalfo]

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

[HKindler]

dass ich die nicht benötigten Spalten nicht ausfiltern kann.

Ich verstehe nicht, was das bedeuten soll….