Spalten mit eindeutigen Überschriften var. machen

Bild

Betrifft: Spalten mit eindeutigen Überschriften var. machen
von: Matthias
Geschrieben am: 22.11.2015 13:48:32

Hallo zusammen,
ich habe eine für euch recht einfache Frage:
Habe ziemlich viel VBA Programmiercode in den letzten gebastelt und nun festgestellt, dass ich hin und wieder
Spalten anspreche, die sich jedoch durch einfügen neuer Spalten verschieben können (und mein Code dann nicht mehr funktioniert). Ich hätte von vornherein das ganze etwas variabler gestalten sollen, dann hätte ich jetzt nicht die Probleme..
Ich wollte nun die erste Zeile mit eindeutigen Überschriften für die Spalten definieren und diese dann ausblenden.
Wie kann ich am einfachsten nun die in Zeile 1 gesetzten Überschriften als Variablen ansprechen?
Muss ich diese mit ".Find" Befehl suchen und dann irgendwie sagen, wenn gefunden dann ist das die richtige Spalte?
Stehe gerade ein wenig auf dem Schlauch wie ich das am einfachsten machen kann...
Vielen Dank für jeden Tipp!
Matti

Bild

Betrifft: AW: Spalten mit eindeutigen Überschriften var. machen
von: Sepp
Geschrieben am: 22.11.2015 13:52:53
Hallo Matti,
.Find ist eine Möglichkeit.


Dim rng as range, lngCol as long
Set rng=Rows(1).Find(What:="Spaltenname", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not rng Is Nothing Then
  lngCol = rng.Column 'Deine Spaltennummer
End if
Gruß Sepp


Bild

Betrifft: AW: Spalten mit eindeutigen Überschriften var. machen
von: Matthias
Geschrieben am: 22.11.2015 14:18:21
Hallo Sepp,
vielen lieben Dank! Das erleichtert nun alles wirklich sehr.
Ein schönes Wochenende noch!
Viele Grüße Matti

Bild

Betrifft: AW: Spalten mit eindeutigen Überschriften var. machen
von: Herbert Grom
Geschrieben am: 22.11.2015 16:30:02
Hallo Matthias,
das sollte auch damit gehen:
intColumn = Range("1:1").Find(What:="Spaltenname").Column
oder so:
intColumn = Cells.Find(What:="Spaltenname", Lookat:=xlWhole).Column
Und falls Du mal den Spaltenbuchstaben brauchst, dann geht das damit:
strColumn = Chr(Cells.Find(What:="Spaltenname", Lookat:=xlWhole).Column + 64)
!Variablendeklaration nicht vergessen!
Excel bietet, wie fast immer, verschiedene Möglichkeiten.
Servus

Bild

Betrifft: aber...
von: Sepp
Geschrieben am: 22.11.2015 16:33:19
Hallo Herbert.
dein Code läuft auf einen Fehler, wenn die Spaltenbezeichnung nicht gefunden wird.
Das mit dem Buchstaben funktioniert nur bis zur Spalte Z.

Gruß Sepp


Bild

Betrifft: geht viel einfacher
von: Michael
Geschrieben am: 22.11.2015 19:28:18
Hi zusammen,
"suchen" ist überhaupt nicht erforderlich, denn der Zugriff auf die "neue" Spalte in Zeile der Tabelle ist ganz simpel: er steht ja beim zu "suchenden" Spaltenbuchstaben in der Zeile Nr. 1.

Option Explicit
Function SpNeu(alt As String) As String
  SpNeu = Range(alt & 1)
End Function
Sub normal()
  Range("G7") = Range("G3")
  Range("H7") = Range("H3")
End Sub
Sub mit_Spneu()
  Range("G8") = Range(SpNeu("G") & 3)
  Range("H8") = Range(SpNeu("H") & 3)
End Sub
Sub Z1_Fuellen()
Dim i As Long
  For i = 1 To 50
    Cells(1, i) = Split(Cells(1, i).AddressLocal, "$")(1)
  Next
End Sub
Wir sind natürlich zu faul, alles hinzuschreiben, also füllt Z1_Fuellen erst Mal die Zeile 1.
Die Function SpNeu liefert den neuen Spaltenbuchstaben, und die beiden anderen Subs zeigen schlicht die Bedienung.
Eine nette Sache wäre, den Algo so zu erweitern, daß nur dann ein geänderter Spaltenbuchstabe verwendet wird, wenn denn einer da steht (dann kann man sich das Fuellen schenken), also etwa so:
Function SpNeuE(alt As String) As String
  SpNeu = Range(alt & 1)
  If SpNeu = "" Then SpNeu = alt
End Function
Schöne Grüße,
Michael

Bild

Betrifft: ? o.T.
von: Sepp
Geschrieben am: 22.11.2015 19:36:07

Gruß Sepp


Bild

Betrifft: Sorry, habe vergessen,
von: Michael
Geschrieben am: 22.11.2015 19:36:35
zu erwähnen, daß ich in G1 ein H und in H1 ein G geschrieben hatte.
Warum nicht gleich mit Datei: https://www.herber.de/bbs/user/101729.xls
Ciao,
M.

Bild

Betrifft: AW: Sorry, habe vergessen,
von: Sepp
Geschrieben am: 22.11.2015 19:46:58
Hallo Michael,
das setzt aber voraus, des man weiß welche Spalten verschoben, oder eingefügt wurden und auch wo hin.
Das war, so glaube ich, nicht die Vorgabe von Matthias.

Gruß Sepp


Bild

Betrifft: AW: Sorry, habe vergessen,
von: Matthias
Geschrieben am: 22.11.2015 20:06:24
Hallo zusammen,
Ja korrekt, es können so viele Spalten entfernt oder hinzugefügt werden und ich weiß nicht mehr genau wo jetzt welche der immer vorhandenen wichtigen Spalten stehen.
vielen Dank für die viele Unterstützung!

Bild

Betrifft: nachgehakt
von: Michael
Geschrieben am: 23.11.2015 16:18:58
Hi zusammen,
jetzt muß doch noch mal nachfragen, ich zitiere: es können so viele Spalten entfernt oder hinzugefügt werden und ich weiß nicht mehr genau wo jetzt welche [...] Spalten stehen
Wie ist die konkrete Situation?
a) Du hast selbst Spalten geändert
b) ein Anwender hat Spalten geändert
c) Du wirst in Zukunft Spalten ändern
d) ein Anwender wird in Zukunft Spalten ändern
zu a) flapsig gesagt, selber Schuld, aber das ist wenig konstruktiv.
zu a) und b) Du hast doch sicher eine Sicherung, mit der Du die geänderte Version vergleichen kannst: mache beide in zwei Fenstern nebeneinander auf, und Du kannst einigermaßen gut nachvollziehen, was wo geändert wurde.
Du HAST doch eine Sicherung? Wenn ich bei einem Projekt Änderungen vornehme, speichere ich die jeweils neueste Version unter dem aktuellen Datum ab, also etwa Projekt20151123.xls(...)
Eine weitere Möglichkeit ist, daß Du in Deinen Code an Stellen, bei denen Du unsicher bist, einfach den entsprechenden Bereich einfärbst - als optische Hilfe. Wenn Du eine Zuweisung zu einer Zelle/einem Bereich hast, kopierst Du einfach die Zeile, entfernst die Zuweisung aus der Kopie und färbst, etwa so:

Zelle=1,234 ' kopieren
Zelle.interior.color=vbyellow

zu c) und d): selbst die gewünschten "Überschriften" werden niemanden davon abhalten, evtl. eine Spalte zu löschen, und dann ist die Spalte samt Überschrift weg, das bringt Dich auch nicht weiter.
Abhilfe: Blatt schützen.
Schöne Grüße,
Michael

Bild

Betrifft: AW: nachgehakt
von: Matthias
Geschrieben am: 23.11.2015 20:10:22
Hallo Michael,
das Szenario ist recht simpel:
Ich habe eine riesige Excel gebaut bei der wöchentlich jemand seinen Senf dazu abgibt und meint hier und da noch etwas ändern zu müssen (so kommen Spalten hinzu oder werden entfernt). Ich habe dann immer die Arbeit und muss jedes mal das Makro anpassen weil die wirklich wichtigen elementaren Spalten mit denen ich im VBA arbeite verschoben sind. Mit der ".Find (What:..." Lösung funktioniert es wunderbar. Wichtig nur: Die Zeile mit den Spaltenüberschriften muss zuvor immer eingeblendet werden, ausgeblendet funktioniert es nicht.
Vielen Dank für die konstruktiven Vorschläge!
Gruß Matthias

Bild

Betrifft: na gut,
von: Michael
Geschrieben am: 24.11.2015 15:05:55
Matthias,
Du scheinst die Sache ja im Griff zu haben. Das Einblenden der Zeile ist in VBA ja auch problemlos.
Trotzdem: wenn einer ne Spalte löscht, ist die schönste Überschrift weg.
Ich an Deiner Stelle hätte da schon längst einen Schrei losgelassen - man programmiert ja nicht zum Spaß.
Weiterhin happy exceling,
Michael

Bild

Betrifft: war wohl ein Schnellschuß,
von: Michael
Geschrieben am: 22.11.2015 20:26:48
Sepp,
denn a) Dein Argument bzgl. der Vorgabe,
aber auch
b) selbst wenn es bekannt ist, wie müßte man vorgehen?
Angenommen, eine neu Spalte B wird eingefügt, dann verschieben sich alle Werte in der ersten Zeile um eine Stelle nach rechts. Kein Problem, Strg+-, Werte nach links verschieben, dann steht C in B1, D in C1 usw., und die Bezüge MIT Fkt. funktionieren weiterhin, nur daß ich auf die neue Spalte B wiederum OHNE Fkt zugreifen muß.
Aber: was ist, wenn ich nochmal ne Spalte B einfüge?
Verwirrt mich gerade etwas - ich muß ohnehin aus dem Haus...
Am Einfachsten wäre halt, wenn Bezüge automatisch im VBE geändert würden: "M" läßt sich ja schön ersetzen, nur die vielen i,j,k auf ranges mit offset, resize und sonstwas, Gates noch?
Vielleicht sind ausgefuchste Zell-/Bereichsnamen das Mittel der Wahl, die kann man rumschieben, wie man will, ohne groß den code ändern zu müssen.
Happy Exceling,
Michael

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Spalten mit eindeutigen Überschriften var. machen"