Office: (Office 2019) Spalten anand der Überschriften ansprechen(ein/ausblenden)

Hallo Leute,
ich habe eine TB in der ich meine Daten mit hilfe einer Case Funktion ein und ausblende.
In der Case Funktion habe ich die Spalten über die Spaltennummern definiert.
Nun bekomme ich aber Daten wo die Spalten nicht immer an der selben Stelle stehen wie ich sie in der Select Case Funktion definiert habe.
Kann mir jemand sagen wie ich anstelle der Sapltenbuschstaben die Überschriften aus der ersten Zeile als Auswahlkriterium benutzen kann?

Anbei die TB mit den kommpletten Code. Ober die Schaltfläche im TB1 wird die UF aktiviert.
Im Modul SW-Finden habe ich schon mal versucht irgendwie umzusetzen. Leider komme ich nicht weiter.

Gruß CX

 

Hallo,

mit folgender Funktion:
worksheetfunction.match("Time",Range("A1:D1"),0)
erhältst du den Wert 3 ("Time" steht in der 3. Zelle des Bereiches A1:D1)
Diese Funktion entspricht der Arbeitsblattfunktion VERGLEICH(...).

 

Hmmm... Irgendwie kann ich dir nicht ganz folgen wie du das meinst.

 

Hallo,

deine Frage war ja doch:

also habe ich dir ein solches Beispiel, das auf deine Datei zutreffen kann, gegeben.
Im aktiven Arbeitsblatt bezeichnet Range("A1:D1") den Bereich der ersten 4 Zellen in der ersten Zeile, der einen Teil der Überschrift enthält. In C1 steht das Wort "Time". Indem du statt der fixen Spaltenbuchstaben (also C1) nach der Überschriftenzelle mit dem Inhalt "Time" suchst, erhältst du mit der Funktion Worksheetfunction.Match("Time", Range("A1:D1"),0) den Wert 3, weil "Time" in der 3. Zelle des Bereiches steht und somit auch in der 3. Spalte.

Du wolltest ja die Überschrifteninhalte als Kriterium zur Spaltensuche.
Außerdem hast du mit folgender Funktion

Code:

Function HoleSpalte(wksTab As Worksheet, strTitel As String) As Long
    Dim rngTreffer As Range

    Set rngTreffer = wksTab.Rows(1).Find(what:=strTitel, lookat:=xlWhole)
    If Not rngTreffer Is Nothing Then
       HoleSpalte = rngTreffer.Column
    End If
End Function

ohnehin eine Funktion, die die Spaltennummer der Stringvariablen strTitel aus der 1.Zeile liefert.
Wenn du folgende SUB in ein allgem.Codemodul einfügst:

Code:

'    Case 1: s = "L:N,AD:AD,AX:AX,BD:BD,BF:BF,BR:BR"                         'C-Stahl
'    case 1: s = "SAMPLE|HEAT|LOT|Mo|Ni|Mn|Cr|Si"
Sub SpaltenEinblendenDetail(s As String)
   Dim ss, Sp As Long
   For Each ss In Split(s, "|")
     Sp = HoleSpalte(Tabelle1, CStr(ss))
     Tabelle1.Columns(Sp).Hidden = False
   Next ss
End Sub

und die Select Case WelcherButton-Code-Passage abänderst, indem du statt den bisherigen s die Spaltenüberschriften einsetzt (getrennt durch "|")

Code:

Dim s As String
Select Case WelcherButton
'    Case 1: s = "L:N,AD:AD,AX:AX,BD:BD,BF:BF,BR:BR"                         'C-Stahl
     Case 1: s = "SAMPLE|HEAT|LOT|Mo|Ni|Mn|Cr|Si"
'    Case 2: s = "L:N,AD:AD,AF:AF,AX:AX,BD:BD,BF:BF,BJ:BJ,BR:BR"             'austenitische Stähle
    Case 2: s = "SAMPLE|HEAT|LOT|Mo|Mb|Ni|Mn|Cr|Ti|Si"
'    Case 3: s = "L:N,AD:AD,AF:AF,AV:AV,AZ:AZ,AX:AX,BD:BD,BF:BF,BJ:BJ,BR:BR" 'Alloy
     Case 3: s = "...."
'    Case 4: s = "L:N,AD:AD,AF:AF,AX:AX,BD:BD,BF:BF,BR:BR"                   'CrNi-Stähle
     Case 4: s = "...."
'    Case 5: s = "L:N,AD:AD,AF:AF,AX:AX,BD:BD,BF:BF,BH:BH,BR:BR"             'Cr-Stähle
     Case 5: s = "...."
'    Case 6: s = "L:N,AD:AD,AF:AF,AX:AX,BD:BD,BF:BF,BH:BH,BR:BR"             'Schweißzusatzwerkstoff
     Case 6: s = "...."
    Case 7: Cells.EntireColumn.Hidden = False: Exit Sub                     'alles Einblenden
End Select
'
SpaltenEinblendenDetail s

und nach dem Select die Sub SpaltenEinblendenDetail mit dem Parameter s aufrufst, müsste das Ganze ebenfalls funktionieren. (Die s für Case 3-6 müsstest du noch ergänzen!)

 

Hi,
hab jetzt den Code mal eingefügt. Im Sub SpaltenEinblendenDetail(s As String) läuft er auf Fehler.
Werd ich mir später noch mal anschauen.

 

Hallo,

ich habe nicht bedacht, dass du sämtliche Spalten ausblendest (und erst hinterdrein einige wieder einblendest).
Hier die korrigierte SUB SpaltenEinblendenDetails(s As String):

Code:

Sub SpaltenEinblendenDetail(s As String)
   Dim Sp() As Long, SpTitel() As String, I As Long
  
  'Alle Spalten einblenden
   Tabelle1.Columns.Hidden = False
  'Spaltennummern Sp zu den Spaltentiteln s ermitteln
  'Spaltennummern für nicht vorhandene Spaltentitel erhalten Sp=0
   SpTitel = Split(s, "|")
   ReDim Sp(UBound(SpTitel)) As Long
   For I = 0 To UBound(SpTitel)
     Sp(I) = HoleSpalte(Tabelle1, SpTitel(I))
   Next I
  
  'Spalten A:CC ausblenden
   SpaltenAusblenden
  'Spalten mit den Spaltennummern Sp, sofern sie nicht 0 sind, wieder einblenden
   For I = 0 To UBound(Sp)
     If Sp(I) Then Tabelle1.Columns(Sp(I)).Hidden = False
   Next I
End Sub

Im Parameter s enthaltene Spaltentitel, die in Zeile 1 von Tabelle1 nicht existieren: Diese Spalten können natürlich auch nicht eingeblendet werden.

 

Hallo ,
ja klar, habe die Spalten vorher umbenannt und dann kann der Code die Spalte nicht finden.
Das habe ich schon abgeändert. Doch ich bekomm beim " alles einblenden" ein Fehler und der Code hängt sich auf. Bin gerade dabei zu schauen woran es liegt.

 

Hallo,

Falls du damit die Zeile "Tabelle1.Columns.Hidden = False" meinst, kann es nur an der nicht existierenden "Tabelle1" liegen (die du offenbar in der Zwischenzeit umbenannt hast). Besser wäre es, statt dieses internen Blattnamens den Tabulatorblattnamen zu verwenden oder den des aktiven Blattes:
Set wksTab = ThisWorkbook.Worksheets("PMI Testdatei") '<-- Ändern falls andere Tab-Bezeichnung
wobei die SUB dann wie folgt aussieht:

Code:

Sub SpaltenEinblendenDetail(s As String)
   Dim wksTab As Worksheet
   Dim Sp() As Long, SpTitel() As String, I As Long
  
  'Arbeitsblatt, in dem Spalten ein/aus-geblendet werden sollen:
   Set wksTab = ThisWorkbook.Worksheets("PMI Testdatei") '<-- Ändern falls andere Tab-Bezeichnung
  
  'Alle Spalten einblenden
   wksTab.Columns.Hidden = False
  'Spaltennummern Sp zu den Spaltentiteln s ermitteln
  'Spaltennummern für nicht vorhandene Spaltentitel erhalten Sp=0
   SpTitel = Split(s, "|")
   ReDim Sp(UBound(SpTitel)) As Long
   For I = 0 To UBound(SpTitel)
     Sp(I) = HoleSpalte(wksTab, SpTitel(I))
   Next I
  
  'Spalten A:CC ausblenden
   wksTab.Columns("A:CC").Hidden = True
  'Spalten mit den Spaltennummern Sp, sofern sie nicht 0 sind, wieder einblenden
   For I = 0 To UBound(Sp)
     If Sp(I) Then wksTab.Columns(Sp(I)).Hidden = False
   Next I
End Sub

Falls du damit eine andere Code-Zeile meinst, müsstest du mir die Zeile mitteilen, an der das Programm hängen bleibt, und mir die Fehlernummer und die Fehlerbeschreibung ebenfalls mitteilen - ansonsten kann ich nämlich nur raten, was du mit dieser dürftigen Fehlerbeschreibung meinst.

Bei mir funktioniert(e) das Makro einwandfrei, wenn ich auf den Button im Arbeitsblatt "PMI Testdatei" klicke.

 

Hi, Sorry klar funktioniert dein Code.
Ich hatte nur eine Zeile zu viel auskommentiert und deshalb bekam ich nen Fehler.
Alles gut .

 

Hallo Exl121150,
wenn ich die Spalte mit "Ltg.Nr" und "Pos.Nr." prüfen will das Ltg.Nr immer vor Pos.Nr. steht sollte es ja ähnlich funktionieren wie mit den Spalten einblenden. Oder?
Ich müsste doch nur die "SpTitel" dann mit einander vergleichen?
Quasi:
Wenn Pos.Nr. vor Ltg.Nr. dann tausche die Spalten sonst mach nichts.

Kannst du mir noch sagen wie ich die Splits ansprechen kann?

Gruß CX

 

Hallo,

wenn du damit meinst, wie die Split(..)-Funktion definiert ist und was sie macht:
Split(Ausdruck As String, [Begrenzer], [Limit As Long=-1], [Vergleich As VbCompareMethod = VbBinaryCompare]) As String()
Gibt ein nullbasiertes eindimensionales Array mit einer bestimmten Anzahl von Teilzeichenfolgen zurück.

Wenn du im VBA-Editor den Eingabe-Cursor auf das Wort "Split" platzierst und die Taste F1 drückst, erhältst du die Microsoft-Hilfe-Dokumentation zu dieser Funktion und ein Beispiel-Demoprogramm. Das ist identisch mit dem folgenden Link:
Split-Funktion (Visual Basic for Applications) | Microsoft Learn

1) Wenn ich dich recht verstanden habe, willst du die beiden Spalten mit Überschrift "Ltg.Nr." (Spalte L=SpaltenNr.12) bzw. "Pos.Nr." (Spalte M=SpaltenNr.13) dann vertauschen, wenn es genau umgekehrt ist, wenn also zB. Spalte "Pos.Nr." in Spalte L liegt und Spalte "Ltg.Nr." in Spalte M.
2) Du musst aber auch hier wieder so vorgehen: Du musst die SpaltenNr., in der "Ltg.Nr." bzw. "Pos.Nr." liegt, mit der Funktion "HoleSpalte(Arbeitsblatt, Spaltenkopfbeichnung)" ermitteln, die Spaltennummern miteinander vergleichen und, falls Punkt 1) zutrifft, eine Vertauschung der Spalten durchführen.
3) Wie du die Spaltennummern aus Punkt 2) zwischenspeicherst, um sie dann danach vergleichen zu können (in ein Array wie ich es getan habe mit Sp(I) oder in 2 einzelnen Variablen zB. SpLtg und SpPos), das ist dir überlassen.

 

Jup so ist es gemeint.
Ah. ok, das werd ich dann mal so versuchen.

 

Hi Exl121150,

wie schaut es denn aus wenn die beiden Spalten mit Überschrift "Ltg.Nr." (Spalte L=SpaltenNr.12) bzw. "Pos.Nr." (Spalte M=SpaltenNr.13) nicht immer in der Spalte 12 und 13 liegen? Ich hab da aktuell 3 Geräte und bei jeden Gerät ist es anders eingestellt. Deshalb hab ich ja gedacht Mann kann die Spaltenüberschriften so miteinander vergleichen. Wenn ich Sie anhand der Ausgabewerte vergleichen will ist es ja auch nicht richtig. oder ? Kann ja schlecht 1 mit 1 vergleichen.

 

Hallo,
ich habe im Folgenden die benutzerdef.Funktion SpaltenTitel_Nr(SpTitelListe As String) As Long() erstellt:

Code:

Public Function SpaltenTitel_Nr(SpTitelListe As String) As Long()
   Dim wksTab As Worksheet
   Dim Sp() As Long, SpTitel() As String, I As Long
  
  'Arbeitsblatt, in dem Spalten ein/aus-geblendet werden sollen:
   Set wksTab = ThisWorkbook.Worksheets("PMI Testdatei") '<-- Ändern falls andere Tab-Bezeichnung
  
  'Spaltennummern Sp zu den Spaltentiteln SpTitelListe ermitteln
  'Spaltennummern für nicht vorhandene Spaltentitel erhalten Sp=0
   SpTitel = Split(SpTitelListe, "|")
   ReDim Sp(UBound(SpTitel)) As Long
   For I = 0 To UBound(SpTitel)
     Sp(I) = HoleSpalte(wksTab, SpTitel(I))
   Next I
   SpaltenTitel_Nr = Sp
End Function

Übergibt man an diese eine Zeichenkette mit Spaltentitelbezeichnungen (getrennt mit "|"), so erhält man ein dynamisches Array mit den dazu gehörigen Spaltennummern zurück.
Wie diese Funktion zu verwenden ist, zeige ich dir in der folgenden Sub SpaltenNr_Vergleiche() im Detail und mit viel Kommentar:

Code:

Public Sub SpaltenNr_Vergleiche()
   Dim TitelTexte As String  'Titelbezeichnungen - getrennt mit "|"
   Dim SpNr() As Long        'Dyn.Array mit den Spaltennummern der Titelbezeichnungen
   Dim I As Long, MsgTxt As String
  
   'Drei Spaltenbezeichnungen werden in "TitelTexte" übergeben an die Funktion "SpaltenTitel_Nr":
   TitelTexte = "Ltg.Nr.|Flags|BATCH"
   SpNr = SpaltenTitel_Nr(TitelTexte)
   'Zu jeder Spaltenbezeichnung wird im dynam.Array SpNr() die entsprechende Spaltennummer gespeichert:
   'Die Spaltennr. für Titel "Ltg.Nr." ist in SpNr(0) enthalten
   'Die Spaltennr. für Titel "Flags"   ist in SpNr(1) enthalten
   'Die Spaltennr. für Titel "BATCH"   ist in SpNr(2) enthalten
   'Für eine nicht vorhandene Spaltenbezeichnung wird in SpNr(...) eine 0 gespeichert.
  
   'Diese (drei) Spaltennummern können zB. mit folgendem Code in einer MessageBox ausgegeben werden:
   MsgTxt = "Die Titeltexte '" & TitelTexte & "' sind " & vbNewLine & "in den Spalten "
   For I = 0 To UBound(SpNr)
     MsgTxt = MsgTxt & SpNr(I) & ", "
   Next I
   MsgBox Prompt:=Left(MsgTxt, Len(MsgTxt) - 2), Title:="Spaltenermittlung"
  
End Sub

Diese Sub kannst du entweder über die Tastenkombination Alt+F8 aufrufen oder du kannst sie auch mit einem Button im Arbeitsblatt verknüpfen.
Beides (die Funktion und die Sub) gehören in ein allgemeines Codemodul gespeichert. Voraussetzung ist das Vorhandensein der Funktion "HoleSpalte(..)".

 

Also wenn ich das jetzt richtig verstanden habe, setze ich da wo die MSGBox -Ausgabe ist mein Vergleich der Spalten ein.
Ich muss ja die Titel mit den dazugehörigen SpNr(..) aus dem Array vergleichen und wenn z.B. Titel "Ltg-Nr."(mit SpNr.(xx)) größer ist als Titel "Flags-Nr."(mit SpNr.(xx)) dann ist ok, Sonst die beiden Spalten miteinander tauschen.
Gibt es zu Spalten tauschen eigentlich eine extrige Funktion oder muss man das über "cut" und "insert" dann machen?